topic Re: how to read in CSV file with one column in SAS Programming

how to read in CSV file with one column

purpleclothlady — Wed, 15 Dec 2021 17:35:22 GMT

hi all:

I am trying to read in a CSV file with DLM="|" , there is only one column

but I would need to a SAS file with different columns

Question:

should I use data step or PROC import ?

thanks a ton.

/*===please see the code ====*/

filename myfile "C:\test.csv" lrecl=10000;

data want2;
infile myfile dlm="|" dsd truncover firstobs=2;
input
STUDYID $
SUBJECT $
DEVICEID $
PLACE $
DATE iso8601.
TOTALSITMIN
RUNMINUTES ;

run;

/*========log error: ==========*/

NOTE: Invalid data for DATE in line 2 20-38.
NOTE: EQUAL SIGN not found.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
2 001|20-1|C1| Wrist|2020-02-12T00:00:00|696|168| 47
STUDYID=001 SUBJECT=20-1 DEVICEID=C1 PLACE=Wrist DATE= INFORMAT= TOTALSITMIN=696 RUNMINUTES=168 _ERROR_=1 _N_=1
NOTE: Invalid data for DATE in line 3 20-38.

/*===please see attached file test.csv==*/

001|20-1|C1| Wrist|2020-02-12T00:00:00|696|168|

001|21-5|C1| Wrist|2020-02-12T00:00:00||69|

001|25-5|C1||2020-02-12T00:00:00|1066|69|

Re: how to read in CSV file with one column

Reeza — Wed, 15 Dec 2021 17:42:36 GMT

CSV means comma (or semicolon) delimited file so you really have a text file with a pipe delimiter not a CSV file.

I would recommend trying PROC IMPORT if you're having issues.
Then you can at least check the code generated in the log to see if it's close to yours or where it's different.

proc import data=want2 datafile="C:\test.csv" dbms=dlm replace;
delimiter='|';
guessingrows=max;
run;

Re: how to read in CSV file with one column

purpleclothlady — Wed, 15 Dec 2021 18:13:24 GMT

hi Reeza:

that is a great solution and explanation and it worked.

thanks so much.

/*===================*/

filename myfile "C;\test.cs" lrecl=10000;

proc import datafile=myfile
out=want

/*****DBMS= specifies that the input file is a delimited external file.

If a ASCII file is delimited by other than a space, you need to explicitly specify delimiter using DELIMITER (or DLM) option with using various characters or combinations of characters as a delimiter.

For example,

DLM='09'x for Tab, DLM='&' for ampersand , DLM='^' for caret, delimiter="|" for pipe which is a space delimited ASCII text file,******/
dbms=dlm
replace;
delimiter='|'; /*specify here the delimiter*/
guessingrows=max;
run;

/*added to make the same format of DATE variable */

data want2;
set want ;
format Date e8601dt19.;
run;

/***please see the difference***/

Re: how to read in CSV file with one column

Tom — Wed, 15 Dec 2021 18:55:21 GMT

There is no ISO informat, and even if there was I doubt it would allow reading of 8,601 bytes. Looks like you want the E8610DT informat instead.

Do not use FORMATTED input mode when reading a delimited file. If you read a fixed number of bytes from the line you might:

1) Read the delimiter in front of the value.

2) Read the delimiter after the value and possibly part of the following value.

3) Not read the full value, which could mess up this variable and reading of the next variable.

If you want to include an informat specification in the INPUT statement then always include the : modifier so that the variable is read using LIST MODE so that the delimiters are honored.

input
  STUDYID $
  SUBJECT $
  DEVICEID $
  PLACE $
  DATE  :E8601DT.
  TOTALSITMIN
  RUNMINUTES 
;

Re: how to read in CSV file with one column

purpleclothlady — Thu, 16 Dec 2021 13:23:06 GMT

Hi Tom:

I updated the code per your suggestion and added format of DATE , here is the result. It works too.

thanks again.

/**====CODE====**/

filename myfile "C:\test.csv" lrecl=10000;

data want2noformat;
infile myfile dlm="|" dsd truncover firstobs=2;
input
STUDYID $
SUBJECT $
DEVICEID $
PLACE $
DATE :E8601DT.
TOTALSITMIN
RUNMINUTES ;
run;

data wantfomat;
infile myfile dlm="|" dsd truncover firstobs=2;
input
STUDYID $
SUBJECT $
DEVICEID $
PLACE $
DATE :E8601DT.
TOTALSITMIN
RUNMINUTES ;
format date e8601dt.;
run;

Re: how to read in CSV file with one column

Tom — Thu, 16 Dec 2021 18:56:09 GMT

Just to be clear that variable does not contain DATE values. It contains DATETIME values. Using a variable name of DATE will likely cause confusion. SAS stores dates as number of days and datetime as number of seconds.