unable to import csv file

[rohithverma]

Hi , i need to import CSV file with date time variable as '_DATE'.But while importing it was throwing an error import unsuccessful and  _DATE" .Could you please help me out on this .

[Kurt_Bremser]

Please supply a few lines of your csv file. Copy/paste them into a window opened with </>. Do NOT open the file with Excel, use a text editor like Notepad++.

[rohithverma]

Please find the corresponding csv file attached below and the variable with "_date" is throwing an error

[art297]

Would need a couple of additional lines from the csv file, as the ones you provided doesn't include any of the data records.

The problem you are confronting is because the _DATE field start with a double quote, followed by a line feed charater, then _DATE, and then ending with a double quote. Minimally, I'd edit the file, using notepad (or similar program that won't destroy the file, and remove the line feed character that comes after the first double quote.

Art, CEO, AnalystFinder.com

[rohithverma]

So can we remove those line feed characters by using proc import in sas .Is this possible with proc import code.

[art297]

We don't have your actual data, so I had to make up some data records to answer your question.

For me, proc import wouldn't work unless I removed the line feed using notepad.

Art, CEO, AnalystFinder.com

[rohithverma]

Please find the updated file with some udpated records and please let em know if any option is available through PROC IMPORT becoz i require only through PROC IMPORT to do this

[Tom]

The problem with the file you posted is that it only has names, and they are split into two lines.

This is because before the underscore in _DATE there is a space and a linefeed.

Please upvote this ballot request to see if we can shame SAS into enhancing their software to support the embedded line-feeds.

https://communities.sas.com/t5/SASware-Ballot-Ideas/Enhancements-to-INFILE-FILE-to-handle-delimited-...

Since this example line is using CR and LF at the end of the line and has just a bare LF in the middle of the _DATE column name you cna actually get SAS to read the file by using the TERMSTR=CRLF option.

See the third data step in log below.

1213  data _null_;
1214    infile 'c:\downloads\ss.csv';
1215    input;
1216    list;
1217  run;

NOTE: The infile 'c:\downloads\ss.csv' is:
      Filename=c:\downloads\ss.csv,
      RECFM=V,LRECL=32767,File Size (bytes)=210,
      Last Modified=13Apr2020:13:30:37,
      Create Time=13Apr2020:13:30:36

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+---
1          REFERENCE NO,DESCRIPTION,ACCOUNTNUM,ACCOUNTNAME,,AMOUNT,MESSAGE_STATUS,CR_ACC,CR_ACC_NA
      89  ME,TRANSACTION NO,CLIENT_CODE,CLIENT_CODE_DESC,MAKER_ID,"  146
2         _DATE",CHECKER_ID,AUTH_DTE,TRAN_TYPE,BENEFICIARY_CD,FILE_NAME 61
NOTE: 2 records were read from the infile 'c:\downloads\ss.csv'.
      The minimum record length was 61.
      The maximum record length was 146.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds


1218  data _null_;
1219    infile 'c:\downloads\ss.csv' termstr=lf;
1220    input;
1221    list;
1222  run;

NOTE: The infile 'c:\downloads\ss.csv' is:
      Filename=c:\downloads\ss.csv,
      RECFM=V,LRECL=32767,File Size (bytes)=210,
      Last Modified=13Apr2020:13:30:37,
      Create Time=13Apr2020:13:30:36

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+---
1          REFERENCE NO,DESCRIPTION,ACCOUNTNUM,ACCOUNTNAME,,AMOUNT,MESSAGE_STATUS,CR_ACC,CR_ACC_NA
      89  ME,TRANSACTION NO,CLIENT_CODE,CLIENT_CODE_DESC,MAKER_ID,"  146
2   CHAR  _DATE",CHECKER_ID,AUTH_DTE,TRAN_TYPE,BENEFICIARY_CD,FILE_NAME. 62
    ZONE  54454224444445544245545454255445555424444444445554424444544440
    NUMR  F41452C3853B52F94C1548F445C421EF4905C25E56939129F34C69C5FE1D5D
NOTE: 2 records were read from the infile 'c:\downloads\ss.csv'.
      The minimum record length was 62.
      The maximum record length was 146.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


1223  data _null_;
1224    infile 'c:\downloads\ss.csv' termstr=crlf;
1225    input;
1226    list;
1227  run;

NOTE: The infile 'c:\downloads\ss.csv' is:
      Filename=c:\downloads\ss.csv,
      RECFM=V,LRECL=32767,File Size (bytes)=210,
      Last Modified=13Apr2020:13:30:37,
      Create Time=13Apr2020:13:30:36

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+---
1          REFERENCE NO,DESCRIPTION,ACCOUNTNUM,ACCOUNTNAME,,AMOUNT,MESSAGE_STATUS,CR_ACC,CR_ACC_NA
      89  ME,TRANSACTION NO,CLIENT_CODE,CLIENT_CODE_DESC,MAKER_ID," ._DATE",CHECKER_ID,AUTH_DTE,TR
    ZONE  4425544544544424424444455444424444455444454454244445544222054454224444445544245545454255
    NUMR  D5C421E31349FE0EFC3C95E4F3F45C3C95E4F3F45F4533CD1B52F94C20AF41452C3853B52F94C1548F445C42
     177  AN_TYPE,BENEFICIARY_CD,FILE_NAME 208
NOTE: 1 record was read from the infile 'c:\downloads\ss.csv'.
      The minimum record length was 208.
      The maximum record length was 208.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

 So to read your 4 line file use code like this:

filename csv 'c:\downloads\ss.csv' termstr=crlf;
proc import file=csv out=test replace dbms=csv;
run;

Note that it will not be able to figure how to name a variable with the name space+linefeed+'_DATE' so it will just named it VAR13.  You can use RENAME statement or RENAME= dataset option to change the name if you want.