Hello,
Can any help how to drop list of columns in a dataset? column names like: SDIAL001POA, SDIAL002POA...SDIAL050POA
this is what I tried but didnot work because after 001 or 002 there're POA.
data want (drop=SDIAL001POA-SDIAL050POA);
set xyz;
run;
Your help is much appreciated!
Thanks,
proc contents data=have out=vars;
run;
proc sql;
select name into :drop_var separated by ' ' from vars
where name like "%POA%" and input(compress(name,, 'kd'), 8.) between 1 and 50;
quit;
data want;
set xyz(drop=&drop_var);
run;
Depends on how your other columns are named, but a wildcard could help. Try for example: data want (drop=SDIA:);
And if you are not using these variables in processing then its better to put drop statement in set statement.
data want ;
set xyz(drop=SDIA:);
run;
Thanks, for the reply. This does not work due to there're other column names similar to this one like SDIAL001PAA- SDIAL050PAA that I want to keep them in my result.
data want;
set xyz(drop=SDIA:);
run;
Maybe brute force 🙂
Data dummy (Drop=i);
Do i=1 To 50;
Drop_me="SDIAL0"!!Put(i,Z2.)!!"POA";
Output;
End;
Run;
Proc SQL NoPrint;
Select Drop_me Into :Drop_me Separated by ' ' From dummy;
Quit;
%Put **&Drop_me.**;
data want (drop=&Drop_me.);
set xyz;
run;
proc contents data=have out=vars;
run;
proc sql;
select name into :drop_var separated by ' ' from vars
where name like "%POA%" and input(compress(name,, 'kd'), 8.) between 1 and 50;
quit;
data want;
set xyz(drop=&drop_var);
run;
Thank you all!!! for your help. Done!
what is the 'kd' stands for in the proc sql section?
proc sql;
select name into :drop_var separated by ' ' from vars
where name like "%POA%" and input(compress(name,, 'kd'), 8.) between 1 and 50;
quit;
kd - keeps digits only.
Can some one please help me to avoid showing this warning after I use this code? WARNING: Apparent invocation of macro POA not resolved.
proc contents data=have out=vars;
run;
proc sql;
select name into :drop_var separated by ' ' from vars
where name like "%POA%" and input(compress(name,, 'kd'), 8.) between 1 and 50;
quit;
data want;
set xyz(drop=&drop_var);
run;
Your problem comes because you are using an ampersand (&) inside double quotes. This indicates to SAS that it should resolve a macro reference POA, but it doesn't find it. To use like in SAS with % then you need to use single quotes so that SAS does not think the % means a macro.
Thanks a lot RW9! it worked.
Well, pop the variables in a macro list:
data have;
length sdial001POA sdial002POA sdial003POA sdial004POA sdial005POA $200.;
sdial001poa="Hello";
output;
run;
proc sql;
select distinct
NAME
into :VLIST separated by " "
from DICTIONARY.COLUMNS
where LIBNAME="WORK"
and MEMNAME="HAVE"
and upcase(compress(NAME,"0123456789"))="SDIALPOA";
quit;
data want;
set have (drop=&VLIST.);
run;
You could then put any processing you want on the metadata before creating the list. You could also avoid macros with:
data _null_;
set sashelp.vcolumn (where=(libname="WORK" and memname="HAVE" and upcase(compress(NAME,"0123456789"))="SDIALPOA")) end=last;
if _n_=1 then call execute('proc sql; alter table work.have ');
call execute(' drop column '||strip(name));
if last then call execute(';quit;');
run;
Are they consecutive variables ?
data want (drop=SDIAL001POA -- SDIAL050POA);
set xyz;
run;
Xia Keshan
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.