BookmarkSubscribeRSS Feed
deleted_user
Not applicable
It has to be Friday before a three day weekend, because I am completely drawing a blank on this -- It should be relatively easy to do, but I'm not seeing it...

Say I have a dataset that looks like this last month:

DATA SUBSET;
.....
FX200601='xxxx'; FX200701='xxxx';
FX200602='xxxx'; FX200702='xxxx';
FX200603='xxxx'; FX200703='xxxx';
FX200604='xxxx'; FX200704='xxxx';
FX200605='xxxx'; FX200705='xxxx';
FX200606='xxxx'; FX200706='xxxx';
FX200608='xxxx'; FX200708='xxxx';
FX200609='xxxx'; FX200709='xxxx';
FX200610='xxxx'; FX200710='xxxx';
FX200611='xxxx'; FX200711='xxxx';
FX200612='xxxx'; FX200712='xxxx';

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?

Thanks
-=James
3 REPLIES 3
1162
Calcite | Level 5
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.

For example:
DATA SUBSET;
FX200601='xxxx'; FX200701='xxxx'; FX200801='xxxx';
FX200602='xxxx'; FX200702='xxxx';
FX200603='xxxx'; FX200703='xxxx';
FX200604='xxxx'; FX200704='xxxx';
FX200605='xxxx'; FX200705='xxxx';
FX200606='xxxx'; FX200706='xxxx';
FX200607='xxxx'; FX200707='xxxx';
FX200608='xxxx'; FX200708='xxxx';
FX200609='xxxx'; FX200709='xxxx';
FX200610='xxxx'; FX200710='xxxx';
FX200611='xxxx'; FX200711='xxxx';
FX200612='xxxx'; FX200712='xxxx';
run;

proc contents data=subset out=contents noprint;
run;

proc sort data=contents (keep=NAME where=(name like 'FX200%')); by descending name; run;

proc sql noprint;
select name into :varlist separated by " "
from contents (obs=24);
quit;

data newset;
set subset (keep=&varlist);
run;
deleted_user
Not applicable
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(*) );
run;
[/pre]

Good Luck

PeterC
deleted_user
Not applicable
Thank you 1162 and Peter - that is very helpful!

I went with 1162's way due to me seeing it first, but appreciate the help!
-=James

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 690 views
  • 0 likes
  • 2 in conversation