topic Re: Date variable calculation in SAS Programming

Date variable calculation

Babloo — Mon, 08 Jan 2018 08:25:58 GMT

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.

Re: Date variable calculation

PeterClemmensen — Mon, 08 Jan 2018 08:29:20 GMT

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;

Re: Date variable calculation

Babloo — Mon, 08 Jan 2018 08:45:43 GMT

I need the Reporting_dt variable to populate the value as 201711

Re: Date variable calculation

PeterClemmensen — Mon, 08 Jan 2018 08:47:27 GMT

Then use

format=yymmn6.

instead 🙂

Re: Date variable calculation

Patrick — Mon, 08 Jan 2018 08:54:12 GMT

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

Re: Date variable calculation

Kurt_Bremser — Mon, 08 Jan 2018 10:10:05 GMT

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.