Keep the same variables for multiple datasets

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Keep the same variables for multiple datasets

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.


Accepted Solutions
Solution
‎03-24-2018 12:05 AM
Super User
Posts: 6,785

Re: Keep the same variables for multiple datasets

[ Edited ]
Posted in reply to liangchh0

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;

View solution in original post


All Replies
New Contributor
Posts: 4

Re: Keep the same variables for multiple datasets

Posted in reply to liangchh0

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.

Super User
Posts: 6,785

Re: Keep the same variables for multiple datasets

[ Edited ]
Posted in reply to liangchh0

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?

 

Occasional Contributor
Posts: 9

Re: Keep the same variables for multiple datasets

Posted in reply to Astounding

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.

Solution
‎03-24-2018 12:05 AM
Super User
Posts: 6,785

Re: Keep the same variables for multiple datasets

[ Edited ]
Posted in reply to liangchh0

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 170 views
  • 0 likes
  • 3 in conversation