BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
liangchh0
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

4 REPLIES 4
reshmape
Fluorite | Level 6

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.

Astounding
PROC Star

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?

 

liangchh0
Fluorite | Level 6

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.

Astounding
PROC Star

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 4 replies
  • 3281 views
  • 0 likes
  • 3 in conversation