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

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

5 REPLIES 5
Oligolas
Barite | Level 11

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 -

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Sean100
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Sean100
Calcite | Level 5
Thanks a million, that worked perfectly!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 5 replies
  • 904 views
  • 0 likes
  • 3 in conversation