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.
%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.
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.
%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.
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;
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.