Only displaying certain variables based on rundate

[mtgkooks]

I have a table with 27 variables: Product, Type, MonthEnd, and then jan_21 through dec_22 (representing 2 years worth of data).

Based on the month in which the program is run, I would only like to show a rolling 12-month set of the date variables, for a total of 15 columns.

For example, if it were to get run today (5/18/22), it would only show the following columns: Product, Type, MonthEnd, jun21, jul21, aug21, sep21, oct21, nov21, dec21, jan22, feb22, mar22, apr22, and may22.

I have tried using something like:

data want;
set have;
if month(today()) - 1 = 4 then;
drop jan21;
drop feb21;
drop mar21;
drop apr21;
drop may21;
drop jun22;
drop jul22;
drop aug22;
drop sep22;
drop oct22;
drop nov22;
drop dec22;
run;

I tried replicating the above code with a series of "else if"s, (if month(today()) - 1 = 5, if month(today()) - 1 = 6, etc.), but I always get the same resultant table.

Any help would be appreciated!

Thanks!

[Reeza · Accepted solution]

data months2keep;
do i=1 to 12;
monthYear = put(intnx('month', today(), 1-i, 'b'), monyy5.);
output;
end;
run;

proc sql noprint;
select monthYear into :months2keep separated by " " from months2keep;
quit;

%put &months2keep.;

data want;
set have;
keep  Product Type MonthEnd &months2keep.;
run;

Probably easier to generate a KEEP list. 

FYI - I would recommend not keeping data in this structure at all - with months/years as variable names. It's problematic for exactly this reason. Instead, typically you keep the data in a long format and then filter using a WHERE statement and transpose for reporting. Much easier to manage, dynamically control and work with in the long run. Bad design makes for more and more bad design. 

[ballardw]

Drop and Keep statements are not executable. So "If <condition> then drop won't do what you want as you found out.

To use an approach that conditionally drops variables with a data step you would have to use macro code that evaluates values set outside of the data.

One of the reasons @Reeza correctly says this is poor data structure is that you would apparently be adding new variables constantly. So any code that works one month may require being rewritten the next time because the actual variables available are different.

As soon as you have a requirement to use values from non-sequential "dates" any code that might work to keep/drop variables would become much more complex as well.