Using Proc SQL to write a simple macro

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

Using Proc SQL to write a simple macro

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

Accepted Solutions
Solution
‎04-13-2018 04:54 PM
Super User
Posts: 13,498

Re: Using Proc SQL to write a simple macro

Posted in reply to UdayGuntupalli

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


All Replies
PROC Star
Posts: 1,767

Re: Using Proc SQL to write a simple macro

Posted in reply to UdayGuntupalli

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

Contributor
Posts: 30

Re: Using Proc SQL to write a simple macro

[ Edited ]
Posted in reply to novinosrin

@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

PROC Star
Posts: 1,767

Re: Using Proc SQL to write a simple macro

Posted in reply to UdayGuntupalli

Oh sorry, i know nothing about it

Respected Advisor
Posts: 2,981

Re: Using Proc SQL to write a simple macro

[ Edited ]
Posted in reply to novinosrin

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
Respected Advisor
Posts: 2,981

Re: Using Proc SQL to write a simple macro

Posted in reply to UdayGuntupalli

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
Contributor
Posts: 30

Re: Using Proc SQL to write a simple macro

Posted in reply to PaigeMiller

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

Solution
‎04-13-2018 04:54 PM
Super User
Posts: 13,498

Re: Using Proc SQL to write a simple macro

Posted in reply to UdayGuntupalli

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.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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