DATA Step, Macro, Functions and more

Variable selection using two datasets

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Variable selection using two datasets

Hi everyone,

I'm relatively new to SAS and I'm struggling with this problem.

 

I've been given two datasets, one big table with hundreds of variables, and the other with one column containing certain variable names from the first dataset.
I need to select all the variables in the first dataset whose names appear in the second dataset.

 

Is there any proc or sql technique to do this?

Thanks


Accepted Solutions
Solution
‎04-27-2018 05:44 AM
Super User
Super User
Posts: 9,799

Re: Variable selection using two datasets

Yes, typo there:

data _null_;
  set small end=last;
  if _n_=1 then call execute('data new_big; set big (keep=');
  call execute(varname||" ");
  if last then call execute('); run;');
run;

View solution in original post


All Replies
Regular Contributor
Posts: 164

Re: Variable selection using two datasets

Hi, yes one way could be using the dictionnary tables:

proc sql ;
   select b.name into :names separated by ' '
   from sashelp.vcolumn a
   inner join
   sashelp.vcolumn b
   on a.name eq b.name
   where a.libname eq 'SASHELP'
   and a.memname eq 'AARFM'
   and b.libname eq 'SASHELP'
   and b.memname eq 'ADSMSG'
   ;
quit;
%put &=names.;

data adsmsg;
   set sashelp.adsmsg;
   keep &names.;
run;
________________________

- Cheers -

Super User
Super User
Posts: 9,799

Re: Variable selection using two datasets

There are several methods.  The one I tend to go with is:

data _null_;
  set small end=last;
  if _n_=1 then call execute('data new_big; set big (keep=');
  call execute(varname," ");
  if last then call execute('); run;');
run;

This will generate the datastep to create new_big, using the big dataset, and for each row in small will add the variable to the keep list.

Occasional Contributor
Posts: 11

Re: Variable selection using two datasets

Hi RW9,

This looks good but I'm getting an error on the second execute routine. It says it has too many arguments.

Any suggestions on a fix for this?

Thanks again

Solution
‎04-27-2018 05:44 AM
Super User
Super User
Posts: 9,799

Re: Variable selection using two datasets

Yes, typo there:

data _null_;
  set small end=last;
  if _n_=1 then call execute('data new_big; set big (keep=');
  call execute(varname||" ");
  if last then call execute('); run;');
run;
Occasional Contributor
Posts: 11

Re: Variable selection using two datasets

Thanks a million, that worked perfectly!
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 135 views
  • 0 likes
  • 3 in conversation