topic Re: subset dataset by variable name in SAS Programming

subset dataset by variable name

Sir_Highbury — Tue, 26 Apr 2016 13:43:57 GMT

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

Re: subset dataset by variable name

RW9 — Tue, 26 Apr 2016 13:10:14 GMT

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&colon;

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.

Re: subset dataset by variable name

Sir_Highbury — Tue, 26 Apr 2016 13:48:29 GMT

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.

Re: subset dataset by variable name

RW9 — Tue, 26 Apr 2016 13:53:58 GMT

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.

Re: subset dataset by variable name

Reeza — Tue, 26 Apr 2016 14:00:28 GMT

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.