SAS PROC Import vs DATA step with INFILE

Question

My PROC IMPORT step is throwing "import unsuccessful" effor when I am trying to read a '~' delimited file containing address field. In the CSV file, 5 byte zip code is automatically treated as a numeric field and once in a while I am getting bad data records with invalid zip codes as VXR1@. When this is encountered I am getting "import unsuccessful" error and the SAS job is failing. PROC IMPORT is automatically getting converted to DATA step with an infile. So I tried DATA step with INFILE option and with INFORMATS and FORMATS and changed the FORMAT of ZIP to alphanumeric. But I faced different issue now. With DATA, INFORMAT and FORMAT keywords, the lengths mismatch is happening and the data is getting moved to different locations automatically. Could someone help me to figure out a solution for this issue?

Included PROC IMPORT I used and DATA file step I used below for reference:

data WORK.TRADER_STATS                               ;
            %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
            infile '/sascode/test/TRADER_STATS.csv' delimiter = '~' MISSOVER DSD lrecl=32767 firstobs=2 ;
               informat TRADER_id best32. ;
               informat dealer_ids $60. ;
               informat dealer_name $27. ;
               informat dealer_city $15. ;
               informat dealer_st $2. ;
               informat dealer_zip $5. ;
               informat SNO best32. ;
               informat start_dt yymmdd10. ;
               informat end_dt yymmdd10. ;
            input
                        TRADER_id
                        dealer_ids $
                        dealer_name $
                        dealer_city $
                        dealer_st $
                        dealer_zip
                        sno
                        start_dt
                        end_dt
           ;
            if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
            run;


proc import file="/sascode/test/TRADER_STATS_BY_DAY.csv" out=WORK.TRADER_STATS_BY_DAY
dbms=dlm replace;
delimiter='~';
;run;

maybe you can use GUESSINGROWS = n where n is big enough for SAS to find a ZIP code with letters, so SAS will get the correct data type. Any chance that you can share your data? — mucio
Try adding a $ sign after the dealer_zip in the input statement though it shouldn't matter. Also, change the missover to truncover. — Reeza

NEOmen NEOmen · Accepted Answer · 2015-04-30T02:47:43

Try Using the : colon operator which will tell SAS to use the informat supplied but to stop reading the value for this variable when a delimiter is encountered, which will sort out your problem of - data getting moved to different locations automatically

data WORK.TRADER_STATS                               ;
            %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
            infile '/sascode/test/TRADER_STATS.csv' delimiter = '~' MISSOVER DSD lrecl=32767 firstobs=2 ;
            input       TRADER_id : best32.
                        dealer_ids : $60.
                        dealer_name : $27.
                        dealer_city : $15. 
                        dealer_st $ : $2. 
                        dealer_zip : $5. 
                        sno : best32. 
                        start_dt : yymmdd10. 
                        end_dt : yymmdd10.;
            if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
            run;

SAS PROC Import vs DATA step with INFILE

1 Answers