subset dataset by variable name

[Sir_Highbury]

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

[RW9]

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.

[Sir_Highbury]

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.

[RW9]

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.

[Reeza · Accepted solution]

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.