topic Drop columns if their labels are listed in another data table. in SAS Enterprise Guide

Drop columns if their labels are listed in another data table.

ayin — Thu, 20 Apr 2017 23:22:02 GMT

Have:

data sample;
	input make $ mpg rep78;
cards;
AMC 22 3
;
run;

data sample2;
set sample;
label	rep78	= "Please drop me"
		mpg	= "Keep me in the dataset"
		make	= "ignore me";
run;

data reference;
	input label_dropped $char32.;
cards;
Please drop me
ignore me
;
run;

Want:

drop columns 'rep78' and 'make' in dataset 'sample2', because their labels are listed in the dataset 'reference' (column 'label_dropped').

How to achieve this?

Thanks.

Re: Drop columns if their labels are listed in another data table.

Reeza — Thu, 20 Apr 2017 23:53:14 GMT

Use SASHELP.VCOLUMN to retrieve the variables with same labels and then add them to a DROP statement.

Ideally you can do the drop via PROC DATASETS or SQL so you don't have to recreate the entire table. I'm not sure that possible though.

Re: Drop columns if their labels are listed in another data table.

ayin — Fri, 21 Apr 2017 00:17:48 GMT

%global droplist;

%macro test;
proc sql;
create table table as
select name, label from sashelp.vcolumn
	where libname = 'WORK' and memname = 'SAMPLE2';

create table table2 as
	select * from table a
	right join reference b
	on b.label_dropped = a.label;

select name into :droplist separated by ' '
from table2;

quit;
%mend test;
%test;

data sample_final;
set sample;

drop &droplist;
run;

Codes are not perfect.

Re: Drop columns if their labels are listed in another data table.

Reeza — Fri, 21 Apr 2017 01:11:26 GMT

Nice work.

You can combine the SQL steps, just make sure that you account for case differences as well. This is untested but hopefully gives you the idea of what to do

proc sql;
create table table as
select name into : droplist separated by " " 
from sashelp.vcolumn
	where libname = 'WORK' and memname = 'SAMPLE2' and upper(label) in (select upper(label_dropped) from reference);
quit;

Re: Drop columns if their labels are listed in another data table.

Reeza — Fri, 21 Apr 2017 01:23:51 GMT

edit:

proc sql noprint;
select name into : droplist separated by " " 
from sashelp.vcolumn
	where libname = 'WORK' and memname = 'SAMPLE2' and upper(label) in (select upper(label_dropped) from reference);
quit;