All,
I am new to SAS and trying to learn where I am going wrong. I am trying to write a simple macro to determine the last day of the month. i have 2 questions with regards to this:
1. Why is the following code failing ?
2. How can I call another macro within this macro definition ?
%Let Input_Month = 2; *Define LastDayOfMonth Macro; %Macro LastDayOfMonth(Input_Month); PROC SQL NoPrint; Select CASE Input_Month WHEN Input_Month In (1,3,5,7,8,10,12) THEN 31 WHEN Input_Month In (4,6,9,11) THEN 30 WHEN Input_Month = 2 THEN 28 ELSE 'unknown' End; %put Result of LastDayOfMonth on Input_Month is &Test; %MEND; * End of Macro LastDayOfMonth; %Let Test = %LastDayOfMonth(&Input_Month);
Multiple issues.
The assignment values you are using are a mix of numeric and character,
you don't end the CASE statement correctly which would look more like:
Select CASE WHEN Input_Month In (1,3,5,7,8,10,12) THEN 31 WHEN Input_Month In (4,6,9,11) THEN 30 WHEN Input_Month = 2 THEN 28 ELSE . as Somevar
The "as variable" assigning the result to a variable,
There is no source supplied for Input_month (macro variable values are referenced as &input_month for example), which Proc SQL is going to expect a data table or view to select from
The proc sql terminates with a "quit;"
Last day of month is YEAR dependent (leap years) so you may have another issue.
But the whole logic involved is not needed if you use the proper date functions:
If you need an all in macro code approach:
%let input_month= 2; %let lastday = %sysfunc(day( %sysfunc(intnx(month, %sysfunc(mdy(&input_month,1,1961)),0,E)))); %put &lastday;
or data step
%let input_month= 2; %let lastday = %sysfunc(day( %sysfunc(intnx(month, %sysfunc(mdy(&input_month,1,1961)),0,E)))); %put &lastday;
I am using a year of 1961 because 1) you don't supply one and 2) that will force February to have 28 days.
Forget the macro , where is the essential FROM clause in a proc sql query or any sql for that matter?
@novinosrin,
You may execute the following code in SQL Studio. This has no FROM clause in it and works perfectly fine.
Declare @Input_Mon Int = 2; Select LastDay = Case When @Input_Mon IN (1,3,5,7,8,10,12) Then 31 When @Input_Mon IN (4,6,9,11) Then 30 When @Input_Mon = 2 Then 28 End
Oh sorry, i know nothing about it
If you have a variable named TODAY which has the value of today's date, then
INTNX('month',TODAY,0,'e')
gives you the last day of today's month
DAY(INTNX('month',TODAY,0,'e')) will give a number like 31 (if the month has 31 days, 30 if the month has 30 days, etc.)
End of the month can easily be determined using the INTCK function. Why do you need more, why do you need a macro or SQL?
@PaigeMiller,
I am learning how to write macros in SAS and practice the same. While I understand this can be done with inbuilt functions, it was my attempt to try and learn and practice developing macros. Appreciate the insight.
Multiple issues.
The assignment values you are using are a mix of numeric and character,
you don't end the CASE statement correctly which would look more like:
Select CASE WHEN Input_Month In (1,3,5,7,8,10,12) THEN 31 WHEN Input_Month In (4,6,9,11) THEN 30 WHEN Input_Month = 2 THEN 28 ELSE . as Somevar
The "as variable" assigning the result to a variable,
There is no source supplied for Input_month (macro variable values are referenced as &input_month for example), which Proc SQL is going to expect a data table or view to select from
The proc sql terminates with a "quit;"
Last day of month is YEAR dependent (leap years) so you may have another issue.
But the whole logic involved is not needed if you use the proper date functions:
If you need an all in macro code approach:
%let input_month= 2; %let lastday = %sysfunc(day( %sysfunc(intnx(month, %sysfunc(mdy(&input_month,1,1961)),0,E)))); %put &lastday;
or data step
%let input_month= 2; %let lastday = %sysfunc(day( %sysfunc(intnx(month, %sysfunc(mdy(&input_month,1,1961)),0,E)))); %put &lastday;
I am using a year of 1961 because 1) you don't supply one and 2) that will force February to have 28 days.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.