This month, new field FX200801='xxxx' will be added to the dataset, but I want to delete off the 25th prior field (FX200601).. In essence I only want the last 24 occurances.. The field names will always have the 4 digit year and month in the name.
Any ideas on how to do this the quickest & easiest way?
I don't know if this is the quickest or easiest, but I'll take a shot . . .
These look like variables (i.e. columns) in a dataset. I would consider using PROC CONTENTS to get a list of the variable names. Filter this list to get the variables that start with 'FX200%'. Then sort descending and put the first 24 occurrences into a macro variable. You can use this macro variable to reference the 24 most recent columns.
just another way ...
since "latest" is defined as the one to add, and the one to remove is two years before, a small amount of work with macro variables should reduce the issue....
(assuming latest is last month)[pre]
%let latest = %sysfunc( intnx( month, "&sysdate9"d, -1), yymmN6 );
%let remove = %sysfunc( intnx( month, "&sysdate9"d, -25), yymmN6 ); [/pre]
Write code so that the new column are added "after" existing columns, then the data steps can use an array statement to refer to the whole picture. [pre]
option symbolgen ;
data new_data_set ;
set old_data_set( drop= FX&remove );
fx&latest = ....some formula... ;
array fxs(24) fx: ; *all columns beginning FX;
*avoid unwanted columns having the FX prefix;
rolling_mean24 = mean( of fxs(*) );