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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.