topic Keep the same variables for multiple datasets in SAS Programming

Keep the same variables for multiple datasets

liangchh0 — Tue, 06 Mar 2018 04:17:14 GMT

Hi everyone,

I'm wondering if there is an efficient way to keep the same variables across multiple datasets in data step.

Consider the following situation

data dsout;

merge dsName1 (keep=var1 var2 var3),

dsName2 (keep=var1 var2 var3),

dsName3 (keep=var1 var2 var3)

;

by var1

run;

Instead of rewriting (keep=var1 var2 var3) after each input dataset, is there a way to tell sas that I'm gonna keep the same variables across many input datasets?

I understand that I could just put keep after dsout and get the same result but that would be much slower for input data set consisting of a large number of variables.

Thanks for your help.

Re: Keep the same variables for multiple datasets

reshmape — Tue, 06 Mar 2018 07:00:44 GMT

Hope this code may be useful,

proc sql;
create table dsout as
select A.var1,A.var2,A.var3,B.var2 as var22,B.var3 as var23,
C.var2 as var32,C.var3 as var33
from dsName1 as A
left join dsName2 as B on A.var1=B.var1
left join dsName3 as C on B.var1=C.var1;
quit;

If you keep same variables overwriting of variable occur.

Re: Keep the same variables for multiple datasets

Astounding — Tue, 06 Mar 2018 09:26:00 GMT

To do this in a DATA step, there is only a partial solution.

%let varlist = var1 var2 var3;

data dsout;

merge dsName1 (keep=&varlist)

dsName2 (keep=&varlist)

dsName3 (keep=&varlist)

;

by var1;

run;

You need to type the list out once, and you need to specify KEEP= for every data set. But you don't need to type out the list multiple times.

Note that you have issues to consider. What will happen when VAR2 comes in from multiple data sets, but there is only room to store one variable named VAR2 in the final result?

Re: Keep the same variables for multiple datasets

liangchh0 — Tue, 06 Mar 2018 09:43:42 GMT

Thanks for your response.

Is it possible to use keep statement just once?

Suppose I have a list of datasets name stored in a macro variable (e.g. the "name" column in sashelp.vcolumn) such that

%put dsList;

returns

dsName1 dsName2 dsName3 ...

Is there a way to merges all the datasets in dsList by calling macro variable &dsList. (i.e. without actually typing the name of each dataset) while applying the same keep statement to each of them?

It would be great if I can do

data dsout;

merge &dsList. (keep=var1 var2 var3);

by var1;

run;

but it is not valid.

Re: Keep the same variables for multiple datasets

Astounding — Tue, 06 Mar 2018 15:43:41 GMT

No. You could write a more complex macro, in order to generate the list of data set names with (keep=) following each name. That takes a bit more macro coding skill but is possible.

******** EDITED: I probably have just enough time to sketch this out so testing is up to you ...

Assuming that macro variables &VARLIST and &DSLIST already exist:

%macro list_plus_keep;

%global newlist;

%let newlist=;

%local k next_dataset;

%do k=1 %to %sysfunc(countw(&dslist));

%let next_dataset = %scan(&dslist, &k);

%let newlist = &newlist &next_dataset (keep=&varlist);

%end;

%mend list_plus_keep;

%list_plus_keep

This gives you a macro variable (&NEWLIST) with all the text you need to insert into the MERGE statement:

data dsout;

merge &newlist;

by var1;

run;