BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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); 
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

 

View solution in original post

7 REPLIES 7
novinosrin
Tourmaline | Level 20

Forget the macro , where is the essential FROM clause in a proc sql query or any sql for that matter?

UdayGuntupalli
Quartz | Level 8

@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 

image.png

novinosrin
Tourmaline | Level 20

Oh sorry, i know nothing about it

PaigeMiller
Diamond | Level 26

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

--
Paige Miller
PaigeMiller
Diamond | Level 26

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?

--
Paige Miller
UdayGuntupalli
Quartz | Level 8

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

ballardw
Super User

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.

 

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 812 views
  • 1 like
  • 4 in conversation