I'm trying to create the variable as Reporting_dt by hardc oding the date value 201711 as given below. But I'm receiving the error as follows as mentioned in the log.
proc sql;
create table new as
select *,'201711'd as Reporting_dt
from sashelp.class
;
quit;
Log:
24 proc sql;
25 create table new as
26 select *,'201711'd as Reporting_dt
ERROR: Invalid date/time/datetime constant '201711'd.
27 from sashelp.class
28 ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
29 quit;
NOTE: The SAS System stopped processing this step because of errors.
Once I create the Reporting_dt variable, I've to use year(Reporting_dt) to create another variable for some calculation. Appreciate if someone of you help me to get past this error.
For hardcoding a date literal in code, SAS has exactly one format, and that is the date9. in quotes with a trailing letter d. Period.
201711 is not a date at all, as the day is missing. So you need to make up a day (first of month, usually) to get a complete date, and then you can apply a display format that only displays year and month (as already suggested).
And I see no value in using
input('201711',yymmn6.)
instead of
'01nov2017'd
as the first option only obfuscates the fact that SAS will create the first of November anyway, as can be seen in this log:
24 data _null_; 25 x1 = input('201711',yymmn6.); 26 put x1= date9.; 27 run; x1=01NOV2017
So in order to keep your code maintainable, use the classic SAS date notation.
The input function should only be used when you have string data (not literals!) that only consists of year and month. And even then I prefer
input(trim(instr)!!'01',yymmdd8.);
for clarity.
I like to use the date9 format when using date constants like this
proc sql;
create table new as
select *
,'01nov2017'd as Reporting_dt format=date9.
from sashelp.class;
quit;
For hardcoding a date literal in code, SAS has exactly one format, and that is the date9. in quotes with a trailing letter d. Period.
201711 is not a date at all, as the day is missing. So you need to make up a day (first of month, usually) to get a complete date, and then you can apply a display format that only displays year and month (as already suggested).
And I see no value in using
input('201711',yymmn6.)
instead of
'01nov2017'd
as the first option only obfuscates the fact that SAS will create the first of November anyway, as can be seen in this log:
24 data _null_; 25 x1 = input('201711',yymmn6.); 26 put x1= date9.; 27 run; x1=01NOV2017
So in order to keep your code maintainable, use the classic SAS date notation.
The input function should only be used when you have string data (not literals!) that only consists of year and month. And even then I prefer
input(trim(instr)!!'01',yymmdd8.);
for clarity.
As @PeterClemmensen points out the 'd syntax requires a string in the form of DDMONYY<YY>
For the string you want to pass in you could use
input('201711',yymmn6.) as Reporting_dt format=date9.
And then apply a format which prints the SAS date value the way you want it, i.e. format=yymmn6.
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.