topic How to automate date in both Date9. and yymmdd10. in data step in SAS Programming

How to automate date in both Date9. and yymmdd10. in data step

LL5 — Tue, 07 Jan 2020 14:53:19 GMT

I need to automate the date by only having the user to hard code the year and month. The below program works perfectly fine for the month of 10, 11 and 12 but did not work for month from 1 to 9. I realized this is due to single digit of month instead of double digit. Could anyone help on this?

Be noted I use this method to atuomate the date is because in the later of the program, there will be SQL pass through between other databases, so I have to automate the date in both of date9. (‘01nov2019’d) And yymmdd10 (2019-11-01) format.

%LET YYYY = 2019; /* year*/

%LET MM = 11; /*month*/

DATA _NULL_;

CURRENT_MONTH = input(cats(&YYYY,&MM,"01"),yymmdd8.) ;

SASD1 = quote(put(CURRENT_MONTH,date9.),"'")||"D" ;

SASD2 = quote(put(intnx('MONTH',CURRENT_MONTH,0 ,"E"),date9.),"'")||"D" ;

SQLD1 = quote(put(CURRENT_MONTH,yymmdd10.),"'") ;

SQLD2 = quote(put(intnx('MONTH',CURRENT_MONTH,0 ,"E"),yymmdd10.),"'") ;

CALL SYMPUT('SASD1',SASD1);

CALL SYMPUT('SASD2',SASD2);

CALL SYMPUT('SQLD1',SQLD1);

CALL SYMPUT('SQLD2',SQLD2);

RUN;

%PUT &SASD1 &SASD2 &SQLD1 &SQLD2;

'01NOV2019'D

'30NOV2019'D

'2019-11-01'

'2019-11-30'

Re: How to automate date in both Date9. and yymmdd10. in data step

novinosrin — Tue, 07 Jan 2020 15:14:32 GMT

Hi @LL5 I wonder how your code worked perfectly fine without quotes around macro variable references within CATS? Well of course CATS does an autoconverson to char with best format, so &yyyy or &mm didn't impact. However the numeric resolution is a problem as 01 would become 1.

CURRENT_MONTH = input(cats(&YYYY,&MM,"01"),yymmdd8.) ;

So, ideally the below should address the issue

CURRENT_MONTH = input(cats("&YYYY","&MM","01"),yymmdd8.) ;

So, with this change,your single digit month value can be assigned into your variables like the below


%LET YYYY = 2019; /* year*/

%LET MM = 01; /*month*/

Re: How to automate date in both Date9. and yymmdd10. in data step

LL5 — Tue, 07 Jan 2020 15:28:59 GMT

Thank you novinosrin, I did not pay attention to the quotes and now with your solution it works well. Thanks again.

Re: How to automate date in both Date9. and yymmdd10. in data step

PaigeMiller — Tue, 07 Jan 2020 15:50:00 GMT

You ought to stop working with calendar dates as text strings. This causes no end of problems. YOu need to work with calendar dates as SAS dates, and then format them to look properly to humans.

%LET YYYY = 2019; /* year*/
%LET MM = 11; /*month*/
DATA _NULL_;
    CURRENT_MONTH = mdy(&mm,1,&yyyy);

Now all your PUT() function calls are unnecessary, and appending a "d" on the end of the string is unnecessary, there is no need to put quotes around anything (and so you can't mess that up) and the result is much cleaner and much more efficient and readable code.

Re: How to automate date in both Date9. and yymmdd10. in data step

Tom — Tue, 07 Jan 2020 15:38:49 GMT

You should probably just use the MDY() function to convert your year and month to a date. Since they take numeric inputs leading zeros are not needed.

CURRENT_MONTH = mdy(&YYYY,&MM,1) ;

Re: How to automate date in both Date9. and yymmdd10. in data step

LL5 — Tue, 07 Jan 2020 16:25:02 GMT

Thanks Tom. This approach worked well.

%LET YYYY = 2020; /* year*/

%LET MM = 01; /*month*/

DATA _NULL_;

CURRENT_MONTH1 = mdy(&mm,1,&yyyy);

D1 = CURRENT_MONTH1;

D2 = intnx('MONTH',CURRENT_MONTH1 ,0 ,"E");

CALL SYMPUT('D1',D1);

CALL SYMPUT('D2',D2);

RUN;

DATA TEST;

A = PUT(&D1, DATE9.);

B = PUT(&D2, DATE9.);

C = PUT(&D1, YYMMDD10.);

D = PUT(&D2, YYMMDD10.);

RUN;

Re: How to automate date in both Date9. and yymmdd10. in data step

LL5 — Tue, 07 Jan 2020 16:27:04 GMT

Thanks PaigeMiller. This method works well down the road. Also thanks for letting me know D is not necessary after the date.