Convert Variable String to date7. format

[RDS2020]

Hi,

Currently in my SQL code written in SAS this is being used:-

Where date_column ='01AUG20'd 

(20 is for last year)

I am trying to change it to make it dynamic and i have prepared below to replace this hard code 

I. Below to make the variable

%LET Last_Year= %substr(%sysfunc(intnx(day,%sysfunc(today()),-365),date7.),3);
/*,3,2); */
%LET Last_YY = 01&Last_Year. ;
%PUT &Last_YY.;

II. This is how i used it but its giving error 

WHERE datecolumn=  '&Last_YY.'d

But this is giving error. how can i use this variable LAST_YY as a date for this WHERE clause

Error:-

Invalid date/time/datetime constant '&Last_YY.'d.

Kindly provide the help.

Thanks,

Raman

[ballardw]

If you want to compare the macro variable to a date value then don't format it just create the date.

If you want the same date as last year you should use %sysfunc( intnx(year, %sysfunc(today()),-1,S)

If you want the previous year then %eval(%sysfunc(year(%sysfunc(today())) -1) might be easier

You really should show what value you think you want to create.

Macro variables do not resolve when they appear inside single quotes. So '&Last_YY.'d is not a valid date at all, "&last_yy"d might. I am not sure what you expect it to be.

[Tom · Accepted solution]

So the immediate cause of the error is that the macro processors ignores strings in single quotes.  Use double quotes instead.

where datecolumn=  "&Last_YY."d

Now to the rest of your code.

Do not use only two digits for years. The extra two bytes won't cost you anything and could save you a LOT of headaches.

What is the -365 in your code?  Are you trying to move back a year? Why use 365 days? What if last year was a leap year?

What is the SUBSTR(xxx,3) for?  Are you trying to remove the day of month so you can replace it with 01?  If so why not use MONTH interval to begin with?

Why do you need to have the date value used in the WHERE clause as a date literal instead of just using the actual number of days since 1960?  The SAS code does not care whether humans can tell what date the number means.

%let last_yy = %sysfunc(intnx(month,%sysfunc(date()),-12,b));
%let last_year = %sysfunc(putn(&last_yy,monyy7.));

Results:

613   %put &=last_yy &=last_year;
LAST_YY=22128 LAST_YEAR=AUG2020

[RDS2020]

Hi @Tom  ,

Thanks for replying , and yes you are on the spot with the solution.

I was try to remove first date and start from month for this .

Didnt wanna to remove 01 from the date as code wasnt mine and didnt wanna to make any unexpected changes.

But appreciate your  help will use them.

Thanks,

Raman