BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Babloo
Rhodochrosite | Level 12

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

5 REPLIES 5
PeterClemmensen
Tourmaline | Level 20

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;
Babloo
Rhodochrosite | Level 12
I need the Reporting_dt variable to populate the value as 201711
PeterClemmensen
Tourmaline | Level 20

Then use 

 

format=yymmn6.

instead 🙂

Kurt_Bremser
Super User

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.

Patrick
Opal | Level 21

@Babloo

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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2179 views
  • 5 likes
  • 4 in conversation