I have a dataset whose column names are col_Dec16, col_Jan17, col_Feb17 and so on. I have created 18 variables to hold a selection of column names as the report I need is to cover 18 months. %let report_date = '15May2018'd; data_null_;
call symput('a01', cat('col_', put(intnx('month',&month1_date,2), monname3.), put(intnx('year',&report_date,-2), year2.))); /* Dec 2 years previous */
call symput('a02', cat('col_', put(intnx('month',&month1_date,3), monname3.), put(intnx('year',&report_date,-1), year2.))); /* Jan 1 years previous */
call symput('a03', cat('col_', put(intnx('month',&month1_date,4), monname3.), put(intnx('year',&report_date,-1), year2.))); /* Feb */
etc..... I would like to create another set of variables that contain a list of the months I want to keep, depending on the month of reporting. Something like; if month(report_date) = May then keeplist = (&a01 &a02 &a03); if month(report_date) = April then keeplist = (&a01 &a02); I don't know if I can do it in a LET statement or if it has to be a macro. Here's what I've tried %macro keeplist;
%if month(&report_date) = 6 %then %let varlist2 = &a01 &a02 -- &a13 &a14 -- &a18;
%if month(&report_date) = 5 %then %let varlist2 = &a01 &a02 -- &a13 &a14 -- &a17;
%if month(&report_date) = 4 %then %let varlist2 = &a01 &a02 -- &a13 &a14 -- &a16;
%mend keeplist;
%keeplist;
proc sql; create table test (keep= &varlist2) as
select *
from items a
left outer join history b
on a.ID = b.ID
quit;
But I get this; ERROR: Required operator not found in expression: month(&report_date) = 6. Another attempt using %LET does allow me to specify the correct columns, but I need to make this conditional on the month. %let keeplist = &a01 &a02 -- &a13 &a14 -- &a16;
proc sql; create table test (keep= &keeplist) as
select *
from items a
left outer join history b
on a.ID = b.ID
quit; Can anyone advise how best to do this? Thanks in advance
... View more