how to drop sequence values in dataset

[AliMN]

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,

[user24feb]

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

[stat_sas]

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;

[AliMN]

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;

[user24feb]

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;

[stat_sas · Accepted solution]

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;

[AliMN]

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

[AliMN]

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;

[stat_sas]

kd - keeps digits only.

[AliMN]

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;

[RW9]

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.

[AliMN]

Thanks a lot RW9! it worked.

[RW9]

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;

[Ksharp]

Are they consecutive variables ?

data want (drop=SDIAL001POA -- SDIAL050POA);

set xyz;

run;

Xia Keshan