DATA Step, Macro, Functions and more

Reading sas date from csv

Reply
Frequent Contributor
Frequent Contributor
Posts: 103

Reading sas date from csv

[ Edited ]

I am trying to read a date column in sas from csv file but it gives some error:

 

error: NOTE: Invalid argument to function INPUT at line 2134 column 11

 

Appreciate  any help to resolve this issue. Thanks

 

data:

have:

id   date

1    27-Apr-12

2    03APR2013

3    11-Mar-13

4   01-Jun-12

my code:

data want;
set have;
format date date9.;
date=upcase(date);

if not missing(date) then do;
lbdt=input(date, ddmmyy9.);
end;

run;

 

Super User
Posts: 5,424

Re: reading sas date from excel to sas

Yeah, your format doesn't match your date on every observation.
Otherwise sure, anydt might work (as a last resort - try to get clean data from your supplier is the no 1 option).
Data never sleeps
Frequent Contributor
Frequent Contributor
Posts: 103

Re: reading sas date from excel to sas

thanks LinusH
Respected Advisor
Posts: 4,919

Re: reading sas date from excel to sas

SAS offers generic date formats such as ANYDTDTE. to handle this kind of poor quality data. Try:

 

data have;
input id   date :$20.;
datalines;
1    27-Apr-12
2    03APR2013
3    11-Mar-13
4   01-Jun-12
;

data want;
set have;
d = input(date, ?? anydtdte.);
format d yymmdd10.;
drop date;
rename d=date;
run;

proc print; run;
PG
Frequent Contributor
Frequent Contributor
Posts: 103

Re: reading sas date from excel to sas

thanks PG
Super User
Super User
Posts: 7,942

Re: reading sas date from excel to sas

Simple answer, clean your data.  27-Apr-12 is not the same as 27APR2012 or in fact 20120427.  What if you have dates in US format:

02012012 - is this 02Jan or 01Feb?  Garbage data will 90% of the time yield garbage results.

 

And a secondary note, CSV is not Excel, CSV=Comam Separated Variable file, which is a plain text file with commas separating data elements.  The fact that Excel has a parser for that doesn't make it an Excel file.

Super User
Posts: 10,018

Re: Reading sas date from csv

data have;
input id   date : date11.;
format date date9.;
datalines;
1    27-Apr-12
2    03APR2013
3    11-Mar-13
4   01-Jun-12
;
run;
Frequent Contributor
Frequent Contributor
Posts: 103

Re: Reading sas date from csv

Thanks Ksharp
Ask a Question
Discussion stats
  • 7 replies
  • 403 views
  • 4 likes
  • 5 in conversation