Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

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

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.

 

 


Accepted Solutions
Solution
Thursday
Contributor
Posts: 58

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

%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.

View solution in original post


All Replies
Grand Advisor
Posts: 15,803

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

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. 

 

 

Solution
Thursday
Contributor
Posts: 58

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

%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.

Grand Advisor
Posts: 15,803

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

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;
Highlighted
Grand Advisor
Posts: 15,803

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

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;
Post a Question
Discussion Stats
  • 4 replies
  • 75 views
  • 3 likes
  • 2 in conversation