topic Process to remove variables from another dataset in SAS Programming

Process to remove variables from another dataset

Danglytics — Tue, 13 Mar 2012 21:48:37 GMT

Hi,

I hope someone could help me with this problem:

I have a data file a.data with accounts and their attributes across ~100 variables

I have a list of variables – as a separate file – a.vars,which contains a subset of 100 variables (let’s say 20 of the 100)

I need to code a process that would drop from a.data all variables that are not in a.vars file. It has to be SAS process (not manualwork through EXCEL), that just does it in a completely automated manner.

a.data:

id var1 var2 var3 var4 var5

1 0 1 1 0 1 0

a.vars:

var1

var2

var3

var6

var7

new.data

id var1 var2 var3

1 0 1 1

Thanks for your help.

Re: Process to remove variables from another dataset

Reeza — Tue, 13 Mar 2012 21:59:09 GMT

Something like the following should work (untested).

You could convert it into a macro if required based on the libname, dataset name and output name easily.

proc sql;

*get columns in your dataset;

create table vnames as

select name from dictionary.columns

where libname=your_libname and memname=yourdataset;

*Get overlap;

select name into :names separated by " " from vnames

where name in (select names from data name_list);

quit;

data new_data;

set <your_data>;

keep &names.;

run;

*Corrected as noted by ballardw

Process to remove variables from another dataset

ballardw — Tue, 13 Mar 2012 22:03:03 GMT

Reeza forgot to include the input dataset.

Data new_data;

set <yourdataset>;

keep &names;

run;

Re: Process to remove variables from another dataset

PGStats — Tue, 13 Mar 2012 22:04:00 GMT

Note : Do not use the period character in SAS dataset names. That's just asking for trouble.

data a_data;
input id var1 var2 var3 var4 var5 var6 var7;
datalines;
1 0 1 1 0 1 0 1 1
;

data a_vars;
input var $;
datalines;
var1
var2
var3
var6
var7
;

proc sql noprint;
select var into :varList separated by " " from a_vars;

data want(keep=&varList); set a_data; run;

proc print; run;

Re: Process to remove variables from another dataset

Reeza — Tue, 13 Mar 2012 22:13:09 GMT

You'd get at least a warning message in your log with this method, as not all variables might be in the dataset.

Process to remove variables from another dataset

Danglytics — Tue, 13 Mar 2012 22:28:48 GMT

thank you for your replies.

proc sql;

*get columns in your dataset;

create table vnames as

select name from dictionary.columns

where libname=your_libname and memname=yourdataset;

do i have to select the variable names in this statement?

Process to remove variables from another dataset

FriedEgg — Tue, 13 Mar 2012 22:35:49 GMT

data foo;

array var[10];

do id=1 to 100;

do _n_=1 to dim(var);

var[_n_]=rand('tabled',.5);

end;

output;

end;

run;

data bar;

input var $;

cards;

var1

var3

var5

var7

var9

;

run;

proc sql noprint;

select b.var into :drops separated by ','

from bar b,

dictionary.columns a

where b.var=a.name;

alter table foo drop column &drops;

quit;

Process to remove variables from another dataset

Astounding — Tue, 13 Mar 2012 22:50:24 GMT

If you are sure that every variable named in A.VARS actually appears in A.DATA, here is the method I like. This assumes that VARNAME is the name of the one and only variable in A.VARS:

data _null_;

call execute('data new.data; set a.data (keep=');

do until (done);

set a.vars end=done;

call execute(varname);

end;

call execute('); run;');

stop;

run;

The log will show you the statements that get generated. Also, note that ID will be dropped if it is not named in A.VARS.

Good luck.

Process to remove variables from another dataset

Reeza — Tue, 13 Mar 2012 23:41:55 GMT

No, it grabs the names of the variables in your dataset.

You only need to specify the library and dataset name in the where clause.

Then the dataset to pull the the names you want to keep in the next query.