topic Re: reading sas date from excel to sas in SAS Programming

Reading sas date from csv

ari — Tue, 11 Oct 2016 20:41:34 GMT

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;

Re: reading sas date from excel to sas

LinusH — Mon, 10 Oct 2016 17:44:41 GMT

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).

Re: reading sas date from excel to sas

PGStats — Mon, 10 Oct 2016 20:58:37 GMT

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;

Re: reading sas date from excel to sas

RW9 — Tue, 11 Oct 2016 08:53:05 GMT

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.

Re: Reading sas date from csv

Ksharp — Tue, 11 Oct 2016 12:37:30 GMT

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;

Re: Reading sas date from csv

ari — Tue, 11 Oct 2016 20:50:19 GMT

Thanks Ksharp

Re: reading sas date from excel to sas

ari — Tue, 11 Oct 2016 20:50:51 GMT

thanks PG

Re: reading sas date from excel to sas

ari — Tue, 11 Oct 2016 20:51:04 GMT

thanks LinusH