DATA Step, Macro, Functions and more

How to read date value from CSV files?

Reply
Frequent Contributor
Posts: 100

How to read date value from CSV files?

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 Smiley Wink . 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!
Super Contributor
Super Contributor
Posts: 365

Re: How to read date value from CSV files?

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
Frequent Contributor
Posts: 100

Re: How to read date value from CSV files?

Thank you. Tested your solution. It worked fine. Agree with you that it's the informat!
SAS Super FREQ
Posts: 8,743

Re: How to read date value from CSV files?

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]
Frequent Contributor
Posts: 100

Re: How to read date value from CSV files?

Thank you. I added : to my codes to read from the two CSV files. They worked!
Ask a Question
Discussion stats
  • 4 replies
  • 871 views
  • 0 likes
  • 3 in conversation