topic Merging Only One Variable from 41 Datasets in SAS Programming

Merging Only One Variable from 41 Datasets

BCNAV — Thu, 26 Jul 2018 13:36:04 GMT

I have 41 datasets that have 7 variables in each one. Each dataset is named based on an air traffic control tower and each variable in the dataset is named based on said control tower. So I have 41 datasets named like CYYZ_PERCENT_OAG_F. The only thing that changes is the first four character tower identifier. Another dataset is called CYVR_PERCENT_OAG_F.

As I wrote above, each data set has seven variables in it. They are:

Year Month CYYZ_COUNT_ALL CYYZ_COUNT_OAG CYYZ_COUNT_NON_OAG CYYZ_OAG_PERCENT CYYZ_NON_OAG_PERCENT

The tower identifier, CYYZ in this case, changes for each variable in each dataset.

I would like to merge all 41 datasets together based on year and month and keep ONLY the CYYZ_OAG_PERCENT variable. So the new dataset would be like:

Year Month CYYZ_OAG_PERCENT CYVR_OAG_PERCENT CYYC_OAG_PERCENT etc.

Is there a way to do this ?

Thanks

-Bill

Re: Merging Only One Variable from 41 Datasets

RW9 — Thu, 26 Jul 2018 13:47:12 GMT

Unfortunately your variable names do not lend themselves to lists or prefixes which could help you here. So you would need to do this manually, into a datastep then keep that. E.g.:

/* This merges all the data - assumes sorted and in a lib called datadir */
data want;
  merge datadir.c:;
  by year month;
run;
/* Get list of percent vars */
proc sql;
  select varname
  into   vlist separated by " "
  from  dictionary.columns
  where libname="WORK"
    and memname="WANT"
    and index(varname,"PERCENT");
quit;
/* Now keep those */
data want (keep=year month &vlist.);
  set want;
run;

Its much easier coding however if you model your data correctly.

Re: Merging Only One Variable from 41 Datasets

Kurt_Bremser — Thu, 26 Jul 2018 14:02:38 GMT

Another classic example why it is a VERY BAD IDEA to have data (tower names) in structure (variable names).

Now you have to go through some complicated macro or call execute() programming to solve something that should be a non-issue in the first place.

And then you go and perpetuate that by making the same, SILLY mistake again.

Your target dataset should be

year month tower oag_percent

If you need a wide format for display in the end, you just need a single proc transpose step. But don't saddle yourself with wide datasets for processing, unless you have a very strong masochistic streak.

Suppose you have a list of towers in a dataset:

data towers;
input tower $;
cards;
CYYZ
CYVR
CYYC
;
run;

data _null_;
call execute('data want; set');
do until (eof);
  set towers end=eof;
  call execute(' library.' !! strip(tower ));
end;
call execute(' indsname=inds;');
call execute('tower=substr(scan(inds,2,"."),1,4);select(tower);');
do until (eof);
  set towers end=eof;
  call execute('when("' !! strip(tower) !! '") oag_percent = ' !! strip(tower) !! '_oag_percent;');
end;
call execute('end; keep year month tower oag_percent; run;');
run;

will give you a nice longitudinal dataset that's easy to deal with.

Re: Merging Only One Variable from 41 Datasets

Astounding — Thu, 26 Jul 2018 14:17:24 GMT

The idea is to create a MERGE statement that looks like this:

merge CYYZ (keep=year month CYYZ_OAG_PERCENT)

CYVR (keep=year month CYVR_OAG_PERCENT)

CYYC (keep=year month CYYC_OAG_PERCENT)

etc.

;

To make this easier, less error-prone, and understandable at the same time, I would recommend creating a small macro:

%macro tower (towername);

&towername (keep=year month &towername._OAG_PERCENT)

%mend tower;

Having done that, you can construct the MERGE statement by calling the macro 41 times:

options mprint;

data want;

merge

%tower (CYYZ)

%tower (CYVR)

%tower (CYYC)

etc.

;

by year month;

run;