how to drop sequence values in dataset

Accepted Solution Solved
Reply
Contributor
Posts: 49
Accepted Solution

how to drop sequence values in dataset

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,



Accepted Solutions
Solution
‎11-18-2014 10:30 AM
Trusted Advisor
Posts: 1,228

Re: how to drop sequence values in dataset

Posted in reply to user24feb

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;

View solution in original post


All Replies
Super Contributor
Posts: 340

Re: how to drop sequence values in dataset

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

Trusted Advisor
Posts: 1,228

Re: how to drop sequence values in dataset

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=SDIASmiley Happy;

run;

Contributor
Posts: 49

Re: how to drop sequence values in dataset

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=SDIASmiley Happy;

run;

Super Contributor
Posts: 340

Re: how to drop sequence values in dataset

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 Smiley Very Happyrop_me Separated by ' ' From dummy;
Quit;
%Put **&Drop_me.**;

data want (drop=&Drop_me.);
set xyz;
run;

Solution
‎11-18-2014 10:30 AM
Trusted Advisor
Posts: 1,228

Re: how to drop sequence values in dataset

Posted in reply to user24feb

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;

Contributor
Posts: 49

Re: how to drop sequence values in dataset

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

Contributor
Posts: 49

Re: how to drop sequence values in dataset

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;

Trusted Advisor
Posts: 1,228

Re: how to drop sequence values in dataset

kd - keeps digits only.

Contributor
Posts: 49

Re: how to drop sequence values in dataset

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;

Super User
Super User
Posts: 7,942

Re: how to drop sequence values in dataset

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.

Contributor
Posts: 49

Re: how to drop sequence values in dataset

Thanks a lot RW9! it worked.

Super User
Super User
Posts: 7,942

Re: how to drop sequence values in dataset

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;

Super User
Posts: 10,023

Re: how to drop sequence values in dataset

Are they consecutive variables ?

data want (drop=SDIAL001POA -- SDIAL050POA);

set xyz;

run;

Xia Keshan

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 440 views
  • 3 likes
  • 5 in conversation