topic Re: How to read date value from CSV files? in SAS Programming

How to read date value from CSV files?

mnew — Wed, 20 Oct 2010 19:49:28 GMT

Greetings:
Trying to figure out an efficient way to read in a very simple CSV file by using Data step (part of my practice for input, infile, informat etc.)... I know how to do this using the import wizard but I simply could not find the way to use a data step for the date field.

CSV data: (comma delimited)
Abby 1/1/2000
Andy 2/1/2001

Here is the code I tried first, which did not give an error message but date values were showing up as missing.

Filename csvTest 'C:\Documents and Settings\Desktop\SAS_Learning\review\test.csv';

data work.testwithoutyear;
infile csvtest dsd missover;
input first_name $ order_date mmddyy10.;
run;

The second puzzle: I added a year value as the third variable. (e.g. Abby, 1/1/2000, 2000).
Then I ran the code with a slightly modified input statement (input first_name $ order_date mmddyy10. order_year 😉 . This time the SAS dataset has the right order_date but the year showed up as 0 for the example.

Any advice? Do you think I should only use Proc Import to read in CSV and give up the Data step?

Thank you!

Re: How to read date value from CSV files?

SPR — Wed, 20 Oct 2010 20:56:04 GMT

Hello Mnew,

I made some experimenting and found that this modification of your code and csv-file works.

a) code (delimiter and format )

data work.testwithoutyear;
infile csvtest delimiter=',' missover;
input first_name $ order_date mmddyy10. year;
format order_date mmddyy10.;
run;

Data: (2 blanks before dates because format requires 10 symbols)

Abby, 1/1/2000,2000
Andy, 2/1/2001,2001

Sincerely,
SPR

Re: How to read date value from CSV files?

Cynthia_sas — Thu, 21 Oct 2010 01:10:04 GMT

Hi:
If you use the colon modifier for the INPUT statement, you will be instructing SAS to read the data using a specific INFORMAT (in this case MMDDYY10.). For more about the colon modifier for INPUT, read the documentation topic entitled: "Modified List Input" which shows examples of the colon format modifier:
http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a003209907.htm

cynthia
[pre]
data work.testdata;
infile datalines delimiter=',' ;
input first_name $ order_date : mmddyy10. year;
format order_date mmddyy10.;
return;
datalines;
Abby,1/1/2000,2000
Andy,2/1/2001,2001
;
run;

ods listing;
proc print data=testdata;
run;
[/pre]

Re: How to read date value from CSV files?

mnew — Sat, 23 Oct 2010 04:25:19 GMT

Thank you. I added : to my codes to read from the two CSV files. They worked!

Re: How to read date value from CSV files?

mnew — Sat, 23 Oct 2010 04:29:51 GMT

Thank you. Tested your solution. It worked fine. Agree with you that it's the informat!