topic Re: How to use a macro in a proc sql statement in SAS Programming

How to use a macro in a proc sql statement

Roddy — Tue, 22 Dec 2015 17:57:28 GMT

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;

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

Reeza — Tue, 22 Dec 2015 18:06:59 GMT

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