Unable to read the file via File Reader Transformation

Babloo — Fri, 23 Feb 2018 07:13:18 GMT

I'm trying to read the CSV file via File Reader Transformation in SAS DI Studio 4.2. My CSV file has 10 variables and 3 observations. After executing the File Reader Transformation, I see that blank values are displayed for all the 3 observations and variables. Appreciate if someone of you guide me to resolve the issue.

My CSV file looks as follows:

Transaction ID,"Policy Number","Plate Number","Client Name","Policy Start Date","Inserted By","Status","Inserted Date","Start Date","End Date"
1,"900203422","1DKB299","Diur. EEN SANItHTY-TEST",2018-02-21,"BOKT","Policy processed successfully",2018-02-16 08:49:32,2018-02-16 08:49:32,2018-02-16 08:50:30

Table which I see after running File Reader Transformation:

Code which was being generated in the File Reader Transformation:


data work.etls_W6X4DRR5 / view = work.etls_W6X4DRR5 ; 
   infile "/data/trans/Transaction_*.csv"
          lrecl = 1200
          delimiter = ','
          dsd
          missover
          firstobs = 2; 
   ; 
   attrib TRANSACTION_ID length = 8
      format = 11.
      informat = 11.; 
   attrib POLICY_NUMBER length = $20
      format = $20.
      informat = $20.; 
   attrib PLATE_NUMBER length = $20
      format = $20.
      informat = $20.; 
   attrib CLIENT_NAME length = $50
      format = $50.
      informat = $50.; 
   attrib POLICY_START_DATE length = $20
      format = $20.
      informat = $20.; 
   attrib INSERTED_BY length = $20
      format = $20.
      informat = $20.; 
   attrib STATUS length = $50
      format = $50.
      informat = $50.; 
   attrib INSERTED_DATE length = 8
      format = DATETIME25.6
      informat = anydtdtm24.; 
   attrib START_DATE length = 8
      format = DATETIME25.6
      informat = anydtdtm24.; 
   attrib END_DATE length = 8
      format = DATETIME25.6
      informat = anydtdtm24.; 
   
   input TRANSACTION_ID POLICY_NUMBER PLATE_NUMBER CLIENT_NAME POLICY_START_DATE 
         INSERTED_BY STATUS INSERTED_DATE START_DATE END_DATE; 
   
run;

Screenshot of my Job window:

Re: Unable to read the file via File Reader Transformation

Patrick — Fri, 23 Feb 2018 08:39:08 GMT

@Babloo

It works for me by just using the code and data you've posted. Ideally post your sample data as attachment and as unchanged as possible.

One theory why things are not working for you: You're in a Windows environment but your source file comes from a Unix/Linux environment and the end-of-line character is only LF and not CRLF.

You're using DIS4.2 which means you're still on SAS 9.2 ....and that's a bad thing as since SAS 9.3 the INFILE statement has a TERMSTR= option for such cases which makes everything very simple.

If it's the LF / CRLF challenge then I'm honestly no more sure how that needs to be approached using SAS9.2. May be the PRINT/NOPRINT option could help?

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000146932.htm

Else you'll have to read the data as byte stream and parse it on your own (which means user written code).

And here the code which I've run successfully:

filename mydata temp;
data _null_;
  file mydata;
  put 'Transaction ID,"Policy Number","Plate Number","Client Name","Policy Start Date","Inserted By","Status","Inserted Date","Start Date","End Date"';
  put '1,"900203422","1DKB299","Diur. EEN SANItHTY-TEST",2018-02-21,"BOKT","Policy processed successfully",2018-02-16 08:49:32,2018-02-16 08:49:32,2018-02-16 08:50:30';
  stop;
run;

data work.etls_W6X4DRR5 / view = work.etls_W6X4DRR5 ; 
   infile mydata
          lrecl = 1200
          delimiter = ','
          dsd
          missover
          firstobs = 2; 
   ; 
   attrib TRANSACTION_ID length = 8
      format = 11.
      informat = 11.; 
   attrib POLICY_NUMBER length = $20
      format = $20.
      informat = $20.; 
   attrib PLATE_NUMBER length = $20
      format = $20.
      informat = $20.; 
   attrib CLIENT_NAME length = $50
      format = $50.
      informat = $50.; 
   attrib POLICY_START_DATE length = $20
      format = $20.
      informat = $20.; 
   attrib INSERTED_BY length = $20
      format = $20.
      informat = $20.; 
   attrib STATUS length = $50
      format = $50.
      informat = $50.; 
   attrib INSERTED_DATE length = 8
      format = DATETIME25.6
      informat = anydtdtm24.; 
   attrib START_DATE length = 8
      format = DATETIME25.6
      informat = anydtdtm24.; 
   attrib END_DATE length = 8
      format = DATETIME25.6
      informat = anydtdtm24.; 
   
   input TRANSACTION_ID POLICY_NUMBER PLATE_NUMBER CLIENT_NAME POLICY_START_DATE 
         INSERTED_BY STATUS INSERTED_DATE START_DATE END_DATE; 
   
run;

topic Re: Unable to read the file via File Reader Transformation in SAS Data Management

Unable to read the file via File Reader Transformation

Re: Unable to read the file via File Reader Transformation