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
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
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.
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: