DATA Step, Macro, Functions and more

Date variable calculation

Accepted Solution Solved
Reply
Super Contributor
Posts: 499
Accepted Solution

Date variable calculation

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.

 


Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 8,358

Re: Date variable calculation

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
PROC Star
Posts: 959

Re: Date variable calculation

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;
Super Contributor
Posts: 499

Re: Date variable calculation

I need the Reporting_dt variable to populate the value as 201711
PROC Star
Posts: 959

Re: Date variable calculation

Then use 

 

format=yymmn6.

instead Smiley Happy

Solution
2 weeks ago
Super User
Posts: 8,358

Re: Date variable calculation

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Highlighted
Respected Advisor
Posts: 4,237

Re: Date variable calculation

[ Edited ]

@Babloo

As @draycut 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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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