topic Re: how to drop sequence values in dataset in SAS Programming

how to drop sequence values in dataset

AliMN — Tue, 18 Nov 2014 14:31:53 GMT

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,

Re: how to drop sequence values in dataset

user24feb — Tue, 18 Nov 2014 14:37:12 GMT

Depends on how your other columns are named, but a wildcard could help. Try for example: data want (drop=SDIA:);

Re: how to drop sequence values in dataset

stat_sas — Tue, 18 Nov 2014 14:40:31 GMT

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;

Re: how to drop sequence values in dataset

AliMN — Tue, 18 Nov 2014 15:09:54 GMT

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;

Re: how to drop sequence values in dataset

user24feb — Tue, 18 Nov 2014 15:18:18 GMT

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;

Re: how to drop sequence values in dataset

stat_sas — Tue, 18 Nov 2014 15:30:03 GMT

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;

Re: how to drop sequence values in dataset

RW9 — Tue, 18 Nov 2014 15:39:29 GMT

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;

Re: how to drop sequence values in dataset

AliMN — Tue, 18 Nov 2014 16:22:09 GMT

Thank you all!!! for your help. Done!

Re: how to drop sequence values in dataset

AliMN — Tue, 18 Nov 2014 16:25:00 GMT

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;

Re: how to drop sequence values in dataset

stat_sas — Tue, 18 Nov 2014 16:36:41 GMT

kd - keeps digits only.

Re: how to drop sequence values in dataset

Ksharp — Wed, 19 Nov 2014 13:54:59 GMT

Are they consecutive variables ?

data want (drop=SDIAL001POA -- SDIAL050POA);

set xyz;

run;

Xia Keshan

Re: how to drop sequence values in dataset

AliMN — Wed, 11 Mar 2015 14:42:57 GMT

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;

Re: how to drop sequence values in dataset

RW9 — Wed, 11 Mar 2015 15:13:35 GMT

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.

Re: how to drop sequence values in dataset

AliMN — Wed, 11 Mar 2015 16:04:14 GMT

Thanks a lot RW9! it worked.