topic Re: read a csv file in SAS Programming

read a csv file

Alexxxxxxx — Wed, 20 Feb 2019 03:39:09 GMT

Dear all,

when I expect to read .csv file by following code,


data Sa_step4.DATASTREAM_GB_o;
infile "I:\20190218\inactive_date_o.csv" DLM = ';' DSD missover lrecl=32767  firstobs = 2 ;
input
 Type :$50.
 NAME :$1000.
 COMPANY_NAME :$1000.
 COMPANY_NAME_1 :$1000.
 COMPANY_NAME___SHORT :$1000.
 PREVIOUS_NAME :$1000.
 GEOGRAPHIC_DESCR_ :$1000.
 MNEMONIC :$1000.
 EQUITIES_STATUS :$1000.
 ISIN_CODE :$100.
 ISIN_NUMBER :$100.
 ISIN_ISSUER_CTRY :$100.
 ISIN_CODE_THAI_FB :$100.
 QUOTE_INDICATOR :$100.
 ISIN_NUMBER_1 :$100.
 INACTIVE_DATE :ddmmyy10.
;
FORMAT
INACTIVE_DATE :ddmmyy10.
;
run;

and get the result like below,

11004  data Sa_step4.DATASTREAM_GB_o;
11005  infile "I:\20190218\inactive_date_o.csv" DLM = ';' DSD missover lrecl=32767  firstobs = 2 ;
11006  input
11007   Type :$50.
11008   NAME :$1000.
11009   COMPANY_NAME :$1000.
11010   COMPANY_NAME_1 :$1000.
11011   COMPANY_NAME___SHORT :$1000.
11012   PREVIOUS_NAME :$1000.
11013   GEOGRAPHIC_DESCR_ :$1000.
11014   MNEMONIC :$1000.
11015   EQUITIES_STATUS :$1000.
11016   ISIN_CODE :$100.
11017   ISIN_NUMBER :$100.
11018   ISIN_ISSUER_CTRY :$100.
11019   ISIN_CODE_THAI_FB :$100.
11020   QUOTE_INDICATOR :$100.
11021   ISIN_NUMBER_1 :$100.
11022   INACTIVE_DATE :ddmmyy10.
11023  ;
11024  FORMAT
11025  INACTIVE_DATE :ddmmyy10.
11026  ;
11027  run;

NOTE: The infile "I:\20190218\inactive_date_o.csv" is:
      Filename=I:\20190218\inactive_date_o.csv,
      RECFM=V,LRECL=32767,File Size (bytes)=2539268,
      Last Modified=20 February 2019 02:58:32 o'cl,
      Create Time=20 February 2019 02:58:30 o'cl

NOTE: Invalid data for INACTIVE_DATE in line 810 195-204.
RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9--
810       32558X;LONDON CAP.GP.HDG.;LONDON CAPITAL GROUP HOLDINGS PLC;LONDON CAP.GP.HDG.PLC.;LONDON CA
      93  PITAL GRP;LONDON CAPITAL GP.HDG.;UNITED KINGDOM;LCG;ACT.;GB00B0RHGY93;GB00B0RHGY93;GB;;P;GB0
     185  0B0RHGY93;02/14/2018 204
Type=32558X NAME=LONDON CAP.GP.HDG. COMPANY_NAME=LONDON CAPITAL GROUP HOLDINGS PLC
COMPANY_NAME_1=LONDON CAP.GP.HDG.PLC. COMPANY_NAME___SHORT=LONDON CAPITAL GRP
PREVIOUS_NAME=LONDON CAPITAL GP.HDG. GEOGRAPHIC_DESCR_=UNITED KINGDOM MNEMONIC=LCG
EQUITIES_STATUS=ACT. ISIN_CODE=GB00B0RHGY93 ISIN_NUMBER=GB00B0RHGY93 ISIN_ISSUER_CTRY=GB
ISIN_CODE_THAI_FB=  QUOTE_INDICATOR=P ISIN_NUMBER_1=GB00B0RHGY93 INACTIVE_DATE=. _ERROR_=1 _N_=809
NOTE: Invalid data for INACTIVE_DATE in line 1507 152-161.
1507      679355;AB AIRLINES DEAD - DEAD 20/06/00;AB AIRLINES PLC;AB AIRLINES;AB AIRLINES PLC;;UNITED
      93  KINGDOM;;DEAD;GB0002713674;GB0002713674;GB;;P;GB0002713674;06/20/2000 161
Type=679355 NAME=AB AIRLINES DEAD - DEAD 20/06/00 COMPANY_NAME=AB AIRLINES PLC
COMPANY_NAME_1=AB AIRLINES COMPANY_NAME___SHORT=AB AIRLINES PLC PREVIOUS_NAME=
GEOGRAPHIC_DESCR_=UNITED KINGDOM MNEMONIC=  EQUITIES_STATUS=DEAD ISIN_CODE=GB0002713674
ISIN_NUMBER=GB0002713674 ISIN_ISSUER_CTRY=GB ISIN_CODE_THAI_FB=  QUOTE_INDICATOR=P
ISIN_NUMBER_1=GB0002713674 INACTIVE_DATE=. _ERROR_=1 _N_=1506
NOTE: Invalid data for INACTIVE_DATE in line 1510 148-157.
1510      902792;AB ELTN.PRDS.;A.B. ELECTRONIC PRODUCTS GROUP PLC;AB ELTN.PRDS.;A.B. ELECTRONIC PROD;;
      93  UNITED KINGDOM;;DEAD;;GB0000011006;N/A;;P;GB0000011006;03/24/1993 157
Type=902792 NAME=AB ELTN.PRDS. COMPANY_NAME=A.B. ELECTRONIC PRODUCTS GROUP PLC
COMPANY_NAME_1=AB ELTN.PRDS. COMPANY_NAME___SHORT=A.B. ELECTRONIC PROD PREVIOUS_NAME=
GEOGRAPHIC_DESCR_=UNITED KINGDOM MNEMONIC=  EQUITIES_STATUS=DEAD ISIN_CODE=  ISIN_NUMBER=GB0000011006
ISIN_ISSUER_CTRY=N/A ISIN_CODE_THAI_FB=  QUOTE_INDICATOR=P ISIN_NUMBER_1=GB0000011006 INACTIVE_DATE=.
_ERROR_=1 _N_=1509
NOTE: Invalid data for INACTIVE_DATE in line 1512 171-180.

Could you please give me some suggestions about this?

I have added the file in the attachment.

Many thanks in advance.

Re: read a csv file

ScottBass — Wed, 20 Feb 2019 04:04:36 GMT

I downloaded your CSV (thanks for that!) and modified your code slightly:

data test;
   infile "\\path\to\file\inactive_date_o.csv" 
   DLM = ';' 
   DSD 
   missover 
   lrecl=32767  
   firstobs = 2;
   input
      Type :$50.
      NAME :$1000.
      COMPANY_NAME :$1000.
      COMPANY_NAME_1 :$1000.
      COMPANY_NAME___SHORT :$1000.
      PREVIOUS_NAME :$1000.
      GEOGRAPHIC_DESCR_ :$1000.
      MNEMONIC :$1000.
      EQUITIES_STATUS :$1000.
      ISIN_CODE :$100.
      ISIN_NUMBER :$100.
      ISIN_ISSUER_CTRY :$100.
      ISIN_CODE_THAI_FB :$100.
      QUOTE_INDICATOR :$100.
      ISIN_NUMBER_1 :$100.
      TEMP_DATE :$10.
   ;
   FORMAT
      INACTIVE_DATE ddmmyy10.
   ;
   INACTIVE_DATE=input(TEMP_DATE,ddmmyy10.);
run;

The first few error lines are:

NOTE: Invalid argument to function INPUT at line 55 column 18.
RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0          
810       32558X;LONDON CAP.GP.HDG.;LONDON CAPITAL GROUP HOLDINGS PLC;LONDON CAP.GP.HDG.PLC.;LONDON CAPITAL GR
     101  P;LONDON CAPITAL GP.HDG.;UNITED KINGDOM;LCG;ACT.;GB00B0RHGY93;GB00B0RHGY93;GB;;P;GB00B0RHGY93;02/14/
     201  2018 204
Type=32558X NAME=LONDON CAP.GP.HDG. COMPANY_NAME=LONDON CAPITAL GROUP HOLDINGS PLC COMPANY_NAME_1=LONDON CAP.GP.HDG.PLC.
COMPANY_NAME___SHORT=LONDON CAPITAL GRP PREVIOUS_NAME=LONDON CAPITAL GP.HDG. GEOGRAPHIC_DESCR_=UNITED KINGDOM
MNEMONIC=LCG EQUITIES_STATUS=ACT. ISIN_CODE=GB00B0RHGY93 ISIN_NUMBER=GB00B0RHGY93 ISIN_ISSUER_CTRY=GB
ISIN_CODE_THAI_FB=  QUOTE_INDICATOR=P ISIN_NUMBER_1=GB00B0RHGY93 >>>TEMP_DATE=02/14/2018<<< INACTIVE_DATE=. _ERROR_=1 _N_=809
NOTE: Invalid argument to function INPUT at line 55 column 18.
1507      679355;AB AIRLINES DEAD - DEAD 20/06/00;AB AIRLINES PLC;AB AIRLINES;AB AIRLINES PLC;;UNITED KINGDOM;
     101  ;DEAD;GB0002713674;GB0002713674;GB;;P;GB0002713674;06/20/2000 161
Type=679355 NAME=AB AIRLINES DEAD - DEAD 20/06/00 COMPANY_NAME=AB AIRLINES PLC COMPANY_NAME_1=AB AIRLINES
COMPANY_NAME___SHORT=AB AIRLINES PLC PREVIOUS_NAME=  GEOGRAPHIC_DESCR_=UNITED KINGDOM MNEMONIC=  EQUITIES_STATUS=DEAD
ISIN_CODE=GB0002713674 ISIN_NUMBER=GB0002713674 ISIN_ISSUER_CTRY=GB ISIN_CODE_THAI_FB=  QUOTE_INDICATOR=P
ISIN_NUMBER_1=GB0002713674 >>>TEMP_DATE=06/20/2000<<< INACTIVE_DATE=. _ERROR_=1 _N_=1506

There are no months with number=14 or 20.

Perhaps you mean mmddyy10. instead?

Re: read a csv file

PGStats — Wed, 20 Feb 2019 04:57:44 GMT

If you don't want to guess how those invalid dates should be interpreted, you can read them as missing values without error messages with

...

INACTIVE_DATE :??ddmmyy10.