topic Re: Oh these lovely dates in SAS Programming

Oh these lovely dates

Lenvdb — Fri, 02 Dec 2016 12:44:02 GMT

I am having a field day with dates today.

Has anyone managed to do this?

We load a daily dataset.

My manager insists we use ISO8601 format, but SAS has no inclination to honour ISO standards.

We put postfix on datasets as _yyyymmdd and declare such dates as Macro variables.

Only when you try to read such a date into a dataset with a Where clause we get terrible problems:

%let dtToday = %sysfunc(date(),yymmddn8.);

Testfile is specified as 
Data TestFile;
Length FileName $30.;
/*===We use a Tab as a delimiter===*/
Infile Datalines dlm='09'x;
Input LoadDate Date9. FileName $ VerNo 2.;
FORMAT LoadDate Date9. FileName $30.;
Datalines;
30NOV2016 20161130_1.zip 1
01DEC2016 20161201_2.zip 2
02DEC2016 20161202_3.zip 3
05DEC2016 20161205_4.zip 4
06DEC2016 20161206_5.zip 5
;
Run;

Data test;
SET TestFile;
where LoadDate = inputn(&dtToday,yymmddn8.);
/*where LoadDate = inputn(&dtToday,Date9.);
where LoadDate = inputn(&dtToday,yymmdd10.);
where LoadDate = "&dtToday"d;*/
/*where LoadDate = &dtToday;*/
run;

SYMBOLGEN: Macro variable DTTODAY resolves to 20161202
32 where LoadDate = inputn(&dtToday,yymmddn8.);
_________
22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
a missing value, (, ), +, ',', -, INPUT, NOT, PUT, ^, ~.

32 where LoadDate = inputn(&dtToday,yymmddn8.);
_________
201
ERROR 201-322: The option is not recognized and will be ignored.

ERROR: Syntax error while parsing WHERE clause.

How do I get around this?

Re: Oh these lovely dates

Kurt_Bremser — Fri, 02 Dec 2016 12:49:43 GMT

Since input() reads from a string, use double quotes:

where LoadDate = inputn("&dtToday",yymmddn8.);

Re: Oh these lovely dates

Kurt_Bremser — Fri, 02 Dec 2016 12:51:51 GMT

And SAS has the e8601* formats for ISO 8601 values since version 9.2.

Re: Oh these lovely dates

Reeza — Fri, 02 Dec 2016 12:54:28 GMT

Date literals are specified in a DATE9 format, or strip the format entirely, or convert in WHERE clause

where Loaddate = input("&dttoday.", yymmdd10.);

%let dtToday = %sysfunc(today());

...

where loadDate = &dtToday;

Re: Oh these lovely dates

Lenvdb — Fri, 02 Dec 2016 13:01:11 GMT

Nope - no success:
SYMBOLGEN: Macro variable DTTODAY resolves to 20161202
33 where LoadDate = inputn("&dtToday",yymmddn8.);
_________
22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
a missing value, (, ), +, ',', -, INPUT, NOT, PUT, ^, ~.

33 where LoadDate = inputn("&dtToday",yymmddn8.);
_________
201
ERROR 201-322: The option is not recognized and will be ignored.

Re: Oh these lovely dates

RW9 — Fri, 02 Dec 2016 13:04:04 GMT

For my 2ps worth:

- Don't put data in filenames. All you are doing is making various things much harder - programming you know how to mess around fing filenames, version control will not work as filename keeps changing etc.

- Don't use macro - which is a text generation tool - to do processing on what is numeric data. Macro has its place, manipulating data is not it. That is what Base SAS is for. Of course, if you follow point 1 above then you don't have this issue.

For instance your where could be:

where LoadDate = today();

Assuming loaddate is a date variable.

Re: Oh these lovely dates

Lenvdb — Fri, 02 Dec 2016 13:05:39 GMT

Thank you so much Reeza

where Loaddate = input("&dttoday.", yymmdd10.);

This worked perfectly...

Now I need a drink....

Pref a strong one!