Creating a 'keep' list of variables

[catmad]

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

[Amir]

Hi,

Can &report_date be in any of the 18 months?

If so, what should the keep list be if it is in the first month and what should the keep list be if it is in the last month?

If not, then what range of months can &report_date be in and could it be in a different year compared to the most recent month?

Regards,

Amir.

[catmad]

No - the report date is 'today' and all of the data relates to previous months. So, a report for today (12th July) would include all months between June 2018 and Jan2017 (18 months)

[Amir]

Hi,

So would the keep list always have 18 variables, e.g., for today col_jan17,...,col_jun18?

Regards,

Amir.

[catmad]

I have got something to work, but it may not be the most efficient method !

data _null_;
if month(&report_date) = 1   and year(&report_date) = 2017 then call symput('k01', "&a01");					/* In Jan, keep Dec */
if month(&report_date) = 2   and year(&report_date) = 2017 then call symput('k01', "&a01 &a02");			/* In Feb, keep Dec & Jan */
if month(&report_date) = 3   and year(&report_date) = 2017 then call symput('k01', "&a01 &a02 -- &a03");	/* In Mar, keep Dec, Jan & Feb */
etc etc

data with_history (keep=&k01);
set history;
run;

[Quentin]

Hi,

To your original question/error, the below code errors because the MONTH() function is not part of the macro language, it's part of the data step language:

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

Luckily, the macro language has a function, %sysfunc, which calls functions from the data step language.  So the below will work.

%macro keeplist;
%global varlist2;
%if %sysfunc(month(&report_date)) = 6 %then %let varlist2 = &a01 &a02 -- &a13 &a14 -- &a18;
...
%mend keeplist;

Note the added %global statement, if this were not there VARLIST2 would be created as a local macro variable.

But I would not recommend this approach. It seems like if you transpose your data, so that instead of having one column for each month you have one record for each month, it would become much easier.

[Amir]

Hi,

The following data step just creates a macro variable for the keep list, not individual macro variables for each column (which should be easy enough to do; just ask if also required):

%let report_date = '15May2018'd;

data _null_;
   length keep_list $ 200;

   do i = -18 to -1;
      keep_list = cat(strip(keep_list), ' ', cat('col_', put(intnx('month',&report_date,i), monyy5.)));
   end;

   call symput('keep_list',keep_list);
run;

%put keep_list = &keep_list;

Regards,

Amir.

[Astounding]

Most likely, you designed the process to be difficult by your choice of column names.  The problem would become much easier to solve (I think) if you were to get rid of these names:

col_Dec16 col_Jan17 ColFeb17

Use these names instead:

col_201612 col_201701 col_201702