Help using Base SAS procedures

selecting columns using an index like in R?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

selecting columns using an index like in R?

Is it possible to select/keep columns in SAS by using an index like in R? (e.g., I want to keep columns 2 through 5 in a dataset with 10 variables). I want to do this without specifying the names (e.g., keep x1 x2 x3).

In R, to see only those columns (without knowing the column names), I would type x[,2:5]


Accepted Solutions
Solution
‎02-20-2015 12:58 PM
PROC Star
Posts: 7,360

Re: selecting columns using an index like in R?

You could use proc sql to create a macro variable.  e.g.:

proc sql;

  select name

    into :vars separated by ' '

      from dictionary.columns

        where libname="SASHELP" and /*must be upper case*/

              memname="CLASS" and /*must be upper case*/

              varnum between 2 and 5

  ;

quit;

data want;

  set sashelp.class (keep=&vars.);

run;

View solution in original post


All Replies
Solution
‎02-20-2015 12:58 PM
PROC Star
Posts: 7,360

Re: selecting columns using an index like in R?

You could use proc sql to create a macro variable.  e.g.:

proc sql;

  select name

    into :vars separated by ' '

      from dictionary.columns

        where libname="SASHELP" and /*must be upper case*/

              memname="CLASS" and /*must be upper case*/

              varnum between 2 and 5

  ;

quit;

data want;

  set sashelp.class (keep=&vars.);

run;

SAS Super FREQ
Posts: 3,475

Re: selecting columns using an index like in R?

An interesting question.  If you know the 2nd and 5th names, you can use the -- operator to get the variable names between them.  I didn't know how to do the general case, but I found a useful paper by Stephanie Thompson that describes how to use PROC SQL to do this operation: http://analytics.ncsu.edu/sesug/2006/CC01_06.PDF

Here is one solution:

proc contents data = Sashelp.Cars noprint out=vars(keep = varnum name);
run;

proc sql noprint;
   select name
   into :keepvars separated by ' '
   from vars
   where 2 <= varnum <= 5
   order by varnum;
quit;

%put &keepvars;

data subset;
   set Sashelp.Cars;
   keep &keepvars;
run;

Occasional Contributor
Posts: 7

Re: selecting columns using an index like in R?

Thanks Art and Rick for the answers! Both solutions work (I was hoping for a solution that didn't use SQL but that's fine).

Trusted Advisor
Posts: 1,128

Re: selecting columns using an index like in R?

Hi ,

Here is an approach of getting the desired output but from datastep without the use of proc sql.

data class;

set sashelp.class;

run;

data columns;

set sashelp.vcolumn;

where libname='WORK' and memname='CLASS' and 2<=varnum<=5;

call symputx("keep"||trim(put(varnum,1.)),name);

run;

%put &keep2 &keep3;

%macro vars;

data class1;

set class;

keep %do j=2 %to 5; &&keep&j %end;;

run;

%mend;

%vars

hope it helps.

Thanks,

Jag

Thanks,
Jag
Super User
Super User
Posts: 6,499

Re: selecting columns using an index like in R?

If you want to treat a series of variables as if they form a matrix then why not just name them using numeric suffixes?  Such X1-X10.  Then it is as easy to select as typing in the index numbers. You can use the LABELS to store more meaning information about the variables.

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 1134 views
  • 2 likes
  • 5 in conversation