如何将含特定SAS函数的SAS SQL脚本转换为Hive SQL？

原SAS脚本核心逻辑拆解 #

原脚本从QUERYFORTEST_1表提取字段，核心做了两件事：

1. 从enc_id字段截取特定子串并转为数值型，生成enid列；
2. 将eu_1至eu_4的缺失值替换为0，生成eu_1b至eu_4b列。

Hive SQL等价转换脚本 #

版本1：仅保证逻辑等价（不做显示格式化）

CREATE TABLE QUERYFORTEST_2
STORED AS ORC -- 可按需修改存储格式，如PARQUET、TEXTFILE等
AS
SELECT t1.date_h,
       t1.en_id,
       t1.eu_1,
       t1.eu_2,
       t1.eu_3,
       t1.eu_4,
       -- 替换SAS的ANYALNUM、ANYALPHA、INPUT函数
       CAST(SUBSTR(t1.enc_id, 
                   regexp_instr(t1.en_id, '[a-zA-Z0-9]', 1, 1, 0), 
                   regexp_instr(t1.en_id, '[a-zA-Z]', 1, 1, 0) - 1
                  ) AS DECIMAL(18,0)) AS enid,
       -- 替换SAS的MISSING函数
       CASE WHEN t1.eu_1 IS NULL THEN 0 ELSE t1.eu_1 END AS eu_1b,
       CASE WHEN t1.eu_2 IS NULL THEN 0 ELSE t1.eu_2 END AS eu_2b,
       CASE WHEN t1.eu_3 IS NULL THEN 0 ELSE t1.eu_3 END AS eu_3b,
       CASE WHEN t1.eu_4 IS NULL THEN 0 ELSE t1.eu_4 END AS eu_4b
FROM QUERYFORTEST_1 t1;

版本2：模拟SAS的COMMA18.显示格式（查询时添加千分位）

如果需要模拟SAS中FORMAT=COMMA18.的显示效果（添加千分位逗号），可以用Hive的format_number函数：

CREATE TABLE QUERYFORTEST_2
STORED AS ORC
AS
SELECT t1.date_h,
       t1.en_id,
       t1.eu_1,
       t1.eu_2,
       t1.eu_3,
       t1.eu_4,
       format_number(CAST(SUBSTR(t1.enc_id, 
                                 regexp_instr(t1.en_id, '[a-zA-Z0-9]', 1, 1, 0), 
                                 regexp_instr(t1.en_id, '[a-zA-Z]', 1, 1, 0) - 1
                                ) AS DECIMAL(18,0)), 0) AS enid,
       format_number(CASE WHEN t1.eu_1 IS NULL THEN 0 ELSE t1.eu_1 END, 0) AS eu_1b,
       format_number(CASE WHEN t1.eu_2 IS NULL THEN 0 ELSE t1.eu_2 END, 0) AS eu_2b,
       format_number(CASE WHEN t1.eu_3 IS NULL THEN 0 ELSE t1.eu_3 END, 0) AS eu_3b,
       format_number(CASE WHEN t1.eu_4 IS NULL THEN 0 ELSE t1.eu_4 END, 0) AS eu_4b
FROM QUERYFORTEST_1 t1;

关键函数替换说明 #

• ANYALNUM：Hive用regexp_instr(str, '[a-zA-Z0-9]', 1, 1, 0)，返回字符串中第一个字母/数字的起始位置；
• ANYALPHA：Hive用regexp_instr(str, '[a-zA-Z]', 1, 1, 0)，返回字符串中第一个字母的起始位置；
• INPUT(...,18.)：SAS转为18位数值，Hive用CAST(...) AS DECIMAL(18,0)或BIGINT（无小数时）实现类型转换；
• MISSING()：SAS判断缺失值（含NULL、.等），Hive中数值列直接用IS NULL判断；
• FORMAT=COMMA18.：SAS是显示格式，Hive无存储级格式，若需显示千分位用format_number函数；
• LABEL：Hive不支持列标签属性，直接移除；
• AS 'enid'：Hive列别名无需单引号，改为AS enid。

内容的提问来源于stack exchange，提问作者CED