topic Re: Using Proc SQL to write a simple macro in SAS Programming

Using Proc SQL to write a simple macro

UdayGuntupalli — Fri, 13 Apr 2018 19:03:33 GMT

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

Re: Using Proc SQL to write a simple macro

novinosrin — Fri, 13 Apr 2018 19:09:33 GMT

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

Re: Using Proc SQL to write a simple macro

PaigeMiller — Fri, 13 Apr 2018 19:21:33 GMT

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?

Re: Using Proc SQL to write a simple macro

ballardw — Fri, 13 Apr 2018 19:22:04 GMT

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.

Re: Using Proc SQL to write a simple macro

UdayGuntupalli — Fri, 13 Apr 2018 20:55:09 GMT

@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

Re: Using Proc SQL to write a simple macro

UdayGuntupalli — Fri, 13 Apr 2018 20:53:28 GMT

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

Re: Using Proc SQL to write a simple macro

novinosrin — Fri, 13 Apr 2018 21:09:20 GMT

Oh sorry, i know nothing about it

Re: Using Proc SQL to write a simple macro

PaigeMiller — Sat, 14 Apr 2018 11:12:29 GMT

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