BookmarkSubscribeRSS Feed
rschubert1
Calcite | Level 5

I have a large data frame already formatted the way I desire with several thousand columns called df1.

 

col1 col2 col3 .... col5000

1      1     1      .... 1

1      0     0      .... 1

1      1     0      .... 1

...     ....   ...     ....  ....

 

I've selected a few hundred of these columns to use as features and have the list stored in a separate dataframe, df2.

ColN

col1

col3

...

col5000

 

The list is approx. 1000 entries and are not sequential. How can I select the columns from df1 so that my final result is something like

col1  col3 .... col5000

1      1      .... 1

1      0      .... 1

1      0      .... 1

....    ....    ....  ....

 

Currently I've tried something like this

 

proc sql;
create table 
	df_subset as
select 
	A.* in(B.colN) 
from 
	df1 as A, 
	df2 as B
quit;

I am currently working in SAS studio

3 REPLIES 3
PaigeMiller
Diamond | Level 26
proc sql noprint;
    select distinct colN into :wanted_columns separated by ' ' from df2;
quit;

data df_subset;
    set df1(keep=&wanted_columns);
run;
--
Paige Miller
ChrisNZ
Tourmaline | Level 20

@rschubert1 Your code doesn't work as you try to use metadata where SQL expects data.

The method used by @PaigeMiller works.

Similarly:

proc sql noprint;
  select distinct colN into :wanted_columns separated by ',' from DF2;
  create table SUBSET as select &wanted_columns from DF1;
run;

 

Reeza
Super User
The term dataframe tells me you're coming from R or Python, SAS uses data sets. Either way, you need to convert that list to a macro variable, similar to creating it as a list in R/Python and then add that to your data frame in a KEEP statement. If you're familiar with R, KEEP is similar in functionality to the SELECT operator in Tidyverse.

PaigeMiller's solution does this, convert the data frame into a variable that can be used in the next steps.

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