BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AliMN
Calcite | Level 5

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,


1 ACCEPTED SOLUTION

Accepted Solutions
stat_sas
Ammonite | Level 13

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

13 REPLIES 13
user24feb
Barite | Level 11

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

stat_sas
Ammonite | Level 13

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
Calcite | Level 5

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
Barite | Level 11

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
Ammonite | Level 13

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
Calcite | Level 5

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

AliMN
Calcite | Level 5

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
Ammonite | Level 13

kd - keeps digits only.

AliMN
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

Thanks a lot RW9! it worked.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

Are they consecutive variables ?

data want (drop=SDIAL001POA -- SDIAL050POA);

set xyz;

run;

Xia Keshan

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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