DATA Step, Macro, Functions and more

subset dataset by variable name

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 127
Accepted Solution

subset dataset by variable name

[ Edited ]

Dear experts,

 

let's assume that I have a adata set containing the following variables:

name, surname, age, address, salary201401, salary201402, salary201403... salary201512

 

I qould like to use a proc sql or a data step to get the following variables:

- name

- all the salaries (i.e. variables contains "salary")

 

Any suggestion?

 

Thanks a lot

 

 

 


Accepted Solutions
Solution
‎04-29-2016 11:32 AM
Super User
Posts: 19,820

Re: subset dataset by variable name

Posted in reply to Sir_Highbury

You can use the colon operator which will keep all variables starting with the prefix indicated. 

 

Data want;
Set have;
Keep name salary:;
Run;

Conversely, if your keeping almost all of the variables you can drop the ones you don't need instead. 

View solution in original post


All Replies
Super User
Super User
Posts: 7,970

Re: subset dataset by variable name

Posted in reply to Sir_Highbury

Sorry, do you want column names?  If so then query sashelp.vcolumns, or dictionary.columns:
data want;  

  set sashelp.vcolumn (where=(libname="WORK" and memname="YOURDATASET"));

run;

 

If you want the values in the coulmns then you can use a generic reference:

data want;
  set have;
  gep=sum(of salary:);
run;

However all that aside, its really not a good idea to name your variables with "data" - in this case a date.  You could end up with lots of variables, and your code will become difficult to work with as the strcuture changes, and even within a datarow may not be the same.  Far better to work with normalised data:

NAME   SURNAME  ADDRESS  SALARY_DATE  SALARY

...

Do remember the data you program on does not need to be the same as output data, you can always transpose at the end.

Frequent Contributor
Posts: 127

Re: subset dataset by variable name

Hi RW9,

sorry, my explanation was not clear. I would like to get the following;

proc sql; create table subset as select name, Salary201401 .... Salary201512 from input; run;

basically I wanna select all the variables containing "salary" plus something else.

Thanks.

Super User
Super User
Posts: 7,970

Re: subset dataset by variable name

Posted in reply to Sir_Highbury

Yes, then my first example would be the one:

proc sql;
  select NAME
  into    :VLIST separated by ","
  from   DICTIONARY.COLUMNS
  where LIBNAME="WORK"
    and  MEMNAME="YOURDATASET"
    and  (NAME="NAME" or upcase(substr(NAME,1,6))="SALARY";
quit;

proc sql;
  select  &VLIST.
  from    HAVE:
quit; 
   

However, I will point out again, that this is very symptomatic of the datastructure you have chosen.  In a normalised data structure your dataset's strcuture does not change, hence you can avoid all these types of problems, and work on processing data, rather than faffing about trying to work out what the structure of your data is.

Solution
‎04-29-2016 11:32 AM
Super User
Posts: 19,820

Re: subset dataset by variable name

Posted in reply to Sir_Highbury

You can use the colon operator which will keep all variables starting with the prefix indicated. 

 

Data want;
Set have;
Keep name salary:;
Run;

Conversely, if your keeping almost all of the variables you can drop the ones you don't need instead. 

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 372 views
  • 2 likes
  • 3 in conversation