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?
Date literals are specified in a DATE9 format, or strip the format entirely, or convert in WHERE clause
where Loaddate = input("&dttoday.", yymmdd10.);
Or
%let dtToday = %sysfunc(today());
...
where loadDate = &dtToday;
Since input() reads from a string, use double quotes:
where LoadDate = inputn("&dtToday",yymmddn8.);
And SAS has the e8601* formats for ISO 8601 values since version 9.2.
Date literals are specified in a DATE9 format, or strip the format entirely, or convert in WHERE clause
where Loaddate = input("&dttoday.", yymmdd10.);
Or
%let dtToday = %sysfunc(today());
...
where loadDate = &dtToday;
Thank you so much Reeza
where Loaddate = input("&dttoday.", yymmdd10.);
This worked perfectly...
Now I need a drink....
Pref a strong one!
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.