How to import formatted .csv data into SAS?

Question

I need to import a 2GB big csv to SAS. There are some variables which are formatted in Excel as general, thus, numbers have thousands seperators (e.g. 1,234.56). When importing to SAS, the error is: NOTE: Invalid data for Settlement_Price in line 111 26-30. And the field is empty. I cannot change the format in Excel because the file is too big. How can I import the raw number? Best12. or 12. does not work.

I tried importing with 12. or best 12.

    %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
    infile 'C:\OP\EoD.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
       informat RIC $12. ;
       informat Settlement_Price best32. ;
       informat Open_Interest best32. ;
       informat Trade_Date ddmmyy10. ;
       informat Volume best32. ;
       format RIC $12. ;
       format Settlement_Price best12. ;
       format Open_Interest best12. ;
       format Trade_Date ddmmyy10. ;
       format Volume best12. ;
    input
                RIC $
                Settlement_Price 
                Open_Interest 
                Trade_Date
                Volume
    ;
    if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
    run;```

Open the text file in a text editor. For the values with embedded commas, is there quotes around the file? — Reeza

Tom Tom · Accepted Answer · 2019-05-01T13:29:12

Assuming that the CSV file is properly formatted and has quotes around the values that contain commas you just need to tell SAS to use the COMMA informat when reading those fields.

data want;
  infile 'C:\OP\EoD.csv' delimiter = ',' TRUNCOVER DSD lrecl=32767 firstobs=2 ;
  length 
    RIC $12
    Settlement_Price 8
    Open_Interest 8 
    Trade_Date 8
    Volume 8 
  ;
  informat 
    Settlement_Price comma.
    Open_Interest comma.
    Trade_Date ddmmyy. 
    Volume comma.
  ;
  format Trade_Date ddmmyy10. ;
  input
    RIC 
    Settlement_Price 
    Open_Interest 
    Trade_Date
    Volume
  ;
run;

How to import formatted .csv data into SAS?

1 Answers