SAS导入竖线分隔文件：如何将点号识别为缺失值？

问题描述 #

我正在导入一个竖线分隔的.txt文件，文件中缺失值用“.”标记（与SAS中数值型缺失值的表示方式一致）。但在导入数值型变量时，SAS将“.”识别为字符型数据，返回Invalid data for ... at line错误。请问是否有办法让SAS将这些点号识别为缺失值？

示例代码 #

data USE.NavENC    ;
    %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
    infile "path" delimiter = '|' MISSOVER DSD lrecl=13106 firstobs=2 ;
       informat PATIENT_ACCOUNT_NUMBER $32. ;
       informat DATE_SERVICE_FROM yymmdd10. ;
       informat DATE_SERVICE_TO yymmdd10. ;
       informat PATIENT_UMPI $10. ;
       informat PATIENT_AGE $5. ;
       informat DATE_PATIENT_DOB yymmdd10. ;
       informat PATIENT_SEX $7. ;
       informat PATIENT_HOME_ZIP_5_DIGIT $5. ;
       informat PATIENT_HOME_COUNTY_NAME $64. ;
       informat FINANCIAL_CLASS_CODE $8. ;
       informat PRIMARY_PAYER_PLAN_CODE $12. ;
       informat PRIMARY_PAYER_PLAN_NAME $100. ;
       informat CHARGES_TOTAL best32. ;
       informat PAYMENTS_TOTAL best32. ;
       informat ADJUSTMENTS_TOTAL best32. ;
       informat ACCOUNT_BALANCE_TOTAL best32. ;
       informat COST_TOTAL best32. ;
       informat COST_VARIABLE $24. ;
       informat COST_FIXED $24. ;
       informat UNITS best32. ;
       informat COST_DIR_VAR_TOTAL best32. ;
       informat COST_DIR_FIX_TOTAL best32. ;
       informat COST_IND_VAR_TOTAL best32. ;
       informat COST_IND_FIX_TOTAL best32. ;
       informat PATIENT_HOME_ADDRESS $57. ;
       informat PATIENT_HOME_CITY $36. ;
       informat PATIENT_HOME_STATE_NAME $37. ;
       format PATIENT_ACCOUNT_NUMBER $32. ;
       format DATE_SERVICE_FROM yymmdd10. ;
       format DATE_SERVICE_TO yymmdd10. ;
       format PATIENT_UMPI $8. ;
       format PATIENT_AGE $5. ;
       format DATE_PATIENT_DOB yymmdd10. ;
       format PATIENT_SEX $7. ;
       format PATIENT_HOME_ZIP_5_DIGIT $5. ;
       format PATIENT_HOME_COUNTY_NAME $64. ;
       format FINANCIAL_CLASS_CODE $4. ;
       format PRIMARY_PAYER_PLAN_CODE $12. ;
       format PRIMARY_PAYER_PLAN_NAME $58. ;
       format CHARGES_TOTAL best12. ;
       format PAYMENTS_TOTAL best12. ;
       format ADJUSTMENTS_TOTAL best32. ;
       format ACCOUNT_BALANCE_TOTAL best32. ;
       format COST_TOTAL best12. ;
       format COST_VARIABLE $24. ;
       format COST_FIXED $24. ;
       format UNITS best12. ;
       format COST_DIR_VAR_TOTAL best12. ;
       format COST_DIR_FIX_TOTAL best12. ;
       format COST_IND_VAR_TOTAL best12. ;
       format COST_IND_FIX_TOTAL best12. ;
       format PATIENT_HOME_ADDRESS $57. ;
       format PATIENT_HOME_CITY $36. ;
       format PATIENT_HOME_STATE_NAME $37. ;
    input
                PATIENT_ACCOUNT_NUMBER
                DATE_SERVICE_FROM
                DATE_SERVICE_TO
                PATIENT_UMPI  $
                PATIENT_AGE $
                DATE_PATIENT_DOB
                PATIENT_SEX  $
                PATIENT_HOME_ZIP_5_DIGIT  $
                PATIENT_HOME_COUNTY_NAME  $
                FINANCIAL_CLASS_CODE  $
                PRIMARY_PAYER_PLAN_CODE
                PRIMARY_PAYER_PLAN_NAME  $
                CHARGES_TOTAL
                PAYMENTS_TOTAL
                ADJUSTMENTS_TOTAL  $
                ACCOUNT_BALANCE_TOTAL  $
                COST_TOTAL
                COST_VARIABLE $
                COST_FIXED $
                UNITS
                COST_DIR_VAR_TOTAL
                COST_DIR_FIX_TOTAL
                COST_IND_VAR_TOTAL
                COST_IND_FIX_TOTAL
                PATIENT_HOME_ADDRESS  $
                PATIENT_HOME_CITY  $
                PATIENT_HOME_STATE_NAME  $
    ;
    if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
    run;

报错信息 #

NOTE: Invalid data for ADJUSTMENTS_TOTAL in line 19 109-112.
19        REDACTED|REDACTRED|REDACTED|REDACTED|REDACTED|REDACTED|RECATED|31204|REDACTED|101|11700113|HU
      91  MANA MA|0.00|0.00|NULL|0.00|0.00|0.00|0.00|1.00|0.00|0.00|0.00|0.00|4217 REDACTED|REDACTED|REDACTED
     181  RGIA 184
PATIENT_ACCOUNT_NUMBER=REDACTRED DATE_SERVICE_FROM=REDACTED DATE_SERVICE_TO=REDACTRED
PATIENT_UMPI=REDACTED PATIENT_AGE=REDACTED DATE_PATIENT_DOB=REDACTED PATIENT_SEX=REDACTED
PATIENT_HOME_ZIP_5_DIGIT=REDACTED PATIENT_HOME_COUNTY_NAME=REDACTED FINANCIAL_CLASS_CODE=101
PRIMARY_PAYER_PLAN_CODE=11700113 PRIMARY_PAYER_PLAN_NAME=HUMANA MA CHARGES_TOTAL=0 PAYMENTS_TOTAL=0
ADJUSTMENTS_TOTAL=. ACCOUNT_BALANCE_TOTAL=0 COST_TOTAL=0 COST_VARIABLE=0.00 COST_FIXED=0.00 UNITS=1
COST_DIR_VAR_TOTAL=0 COST_DIR_FIX_TOTAL=0 COST_IND_VAR_TOTAL=0 COST_IND_FIX_TOTAL=0
PATIENT_HOME_ADDRESS=REDACTED PATIENT_HOME_CITY=REDACTED PATIENT_HOME_STATE_NAME=REDACTED _ERROR_=1
_N_=18

解决方法 #

1. 修正变量类型定义错误 #

你的代码存在明显矛盾：ADJUSTMENTS_TOTAL和ACCOUNT_BALANCE_TOTAL的informat被定义为数值型best32.，但在input语句后却加了$符号，强制SAS按字符型读取，这是报错的直接原因。

修改input语句，去掉这两个变量后的$：

input
    PATIENT_ACCOUNT_NUMBER
    DATE_SERVICE_FROM
    DATE_SERVICE_TO
    PATIENT_UMPI  $
    PATIENT_AGE $
    DATE_PATIENT_DOB
    PATIENT_SEX  $
    PATIENT_HOME_ZIP_5_DIGIT  $
    PATIENT_HOME_COUNTY_NAME  $
    FINANCIAL_CLASS_CODE  $
    PRIMARY_PAYER_PLAN_CODE
    PRIMARY_PAYER_PLAN_NAME  $
    CHARGES_TOTAL
    PAYMENTS_TOTAL
    ADJUSTMENTS_TOTAL  /* 移除$符号 */
    ACCOUNT_BALANCE_TOTAL  /* 移除$符号 */
    COST_TOTAL
    COST_VARIABLE $
    COST_FIXED $
    UNITS
    COST_DIR_VAR_TOTAL
    COST_DIR_FIX_TOTAL
    COST_IND_VAR_TOTAL
    COST_IND_FIX_TOTAL
    PATIENT_HOME_ADDRESS  $
    PATIENT_HOME_CITY  $
    PATIENT_HOME_STATE_NAME  $
;

修正后，SAS会自动将数值型字段中的.识别为缺失值，无需额外配置。

2. 处理文件中的其他缺失值标记（如NULL） #

从报错日志可以看到，文件中还存在NULL作为缺失值的情况，若要同时处理.和NULL，可以采用以下方法：

方法A：使用??修饰符跳过无效数据

在数值型变量前添加??，让SAS跳过无效数据错误，同时将无法转换的字符（如NULL）设为缺失值：

input
    /* ...其他变量... */
    ?? ADJUSTMENTS_TOTAL
    ?? ACCOUNT_BALANCE_TOTAL
    /* ...其他变量... */
;

方法B：自定义Informat映射缺失值

创建自定义输入格式，将.和NULL统一映射为SAS的数值型缺失值：

proc format;
    invalue miss_num
        '.' = .
        'NULL' = .
        other = [best32.]  /* 其他正常数值按best32.读取 */
    ;
run;

然后将数值型变量的informat替换为这个自定义格式：

informat ADJUSTMENTS_TOTAL miss_num.;
informat ACCOUNT_BALANCE_TOTAL miss_num.;
informat CHARGES_TOTAL miss_num.;
/* 其他需要处理的数值型变量同理 */

3. 验证结果 #

修改后重新运行代码，检查日志是否消除Invalid data错误，同时查看数据集，确认缺失值已被正确识别为SAS的.。

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