DATA Step, Macro, Functions and more

How to use a macro in a proc sql statement

Reply
Occasional Contributor
Posts: 5

How to use a macro in a proc sql statement

I created this macro:

%LET date = %SYSFUNC(today());
%LET startmonth = %STR(%")%SYSFUNC(intnx(month,&date,-6),yymmn6.)%STR(%");
%LET endmonth = %STR(%")%SYSFUNC(intnx(month,&date,-4),yymmn6.)%STR(%");
%PUT &startmonth &endmonth;

 

I want to the variable YearMonth to fall in between my startmonth and endmonth macros. Using the below statement gives me the error: Expression using IN has components that are of different data types.  YearMonth is (float,null).

PROC SQL;
CREATE TABLE Policy AS
SELECT
A.Policy,
A.PlanCode,
A.I_EmpID,
A.YearMonth
FROM
TestData A
WHERE
A.System = 'NPS'
AND DistributionChannel = 'Direct'
AND "&startmonth" <= YearMonth <= "&endmonth"
;
QUIT;

 

Super User
Posts: 19,770

Re: How to use a macro in a proc sql statement

Macro variable is text replacement, so your macro variables are replaced where you indicate which resolves to:

11568 %PUT &startmonth &endmonth;
"201506" "201508"


Which translates to
"&startmonth" <= YearMonth <= "&endmonth"
""201506"" <= YearMonth <= ""201508""

Issues with this:
1. too many quotation marks
2. Types don't match char vs num.

Fix:
1. Remove quotes from macro variable
2. Remove quotes from And condition

%LET date = %SYSFUNC(today());
%LET startmonth = %SYSFUNC(intnx(month,&date,-6),yymmn6.);
%LET endmonth = %SYSFUNC(intnx(month,&date,-4),yymmn6.);
%PUT &startmonth &endmonth;

AND &startmonth <= YearMonth <= &endmonth
Ask a Question
Discussion stats
  • 1 reply
  • 137 views
  • 0 likes
  • 2 in conversation