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

Hi Everyone,

I want to delete all columns that have name with string "ID" in it. 

My data is like that

 

data have;
input abIc ID1 bxI4D b_ID2 xyzID_3;
cards;
1 2 3 4 5
;run;

 

So I only keep abIc and bxI4D.

 

Thank you for your help.

 

HC

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
data have;
input abIc ID1 bxI4D b_ID2 xyzID_3; 
cards;
1 2 3 4 5
;run;

proc sql;
select name into :drop_list separated by ','
from sashelp.vcolumn
where libname="WORK" and memname="HAVE" and index(name,'ID')>0;
quit;

%put &drop_list;

proc sql;
   alter table have
      drop  &drop_list;
quit;

View solution in original post

8 REPLIES 8
Reeza
Super User

Not easily but you can build a list of variables from either PROC CONTENTS or SASHELP.VCOLUMN which has the list of variables. 

Then include those in your DROP statement. 

novinosrin
Tourmaline | Level 20
data have;
input abIc ID1 bxI4D b_ID2 xyzID_3; 
cards;
1 2 3 4 5
;run;

proc sql;
select name into :drop_list separated by ' '
from sashelp.vcolumn
where libname="WORK" and memname="HAVE" and index(name,'ID')>0;
quit;

%put &drop_list;

data have;
set have;
drop &drop_list;
run;

novinosrin
Tourmaline | Level 20
data have;
input abIc ID1 bxI4D b_ID2 xyzID_3; 
cards;
1 2 3 4 5
;run;

proc sql;
select name into :drop_list separated by ','
from sashelp.vcolumn
where libname="WORK" and memname="HAVE" and index(name,'ID')>0;
quit;

%put &drop_list;

proc sql;
   alter table have
      drop  &drop_list;
quit;
hhchenfx
Barite | Level 11

Thanks a lot.

HHC

hhchenfx
Barite | Level 11

So weird! 

SAS only take all letter in CAP to run that code.

 

Here is the log:

 

3291 proc sql;
3292 select name into :drop_list separated by ','
3293 from sashelp.vcolumn
3294 where libname="WORK" and memname="myfile" and index(name,'ID')>0;
NOTE: No rows were selected.
3295 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds


3296
3297 proc sql;
3298 select name into :drop_list separated by ','
3299 from sashelp.vcolumn
3300 where libname="WORK" and memname="MYFILE" and index(name,'ID')>0;
3301 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.00 seconds

novinosrin
Tourmaline | Level 20

memname is always stored as UPCASE in metadata

art297
Opal | Level 21

@novinosrin: Maybe that's true for Windows, but not for all operating systems. That's why I suggested the find function using the 'i' modifier.

 

Oops! Just read your post more carefully! Yes, libname and memname have to be in upper case. However, the variable names are case sensitive on some operating systems.

 

Art, CEO, AnalystFinder.com

 

art297
Opal | Level 21

 

Same solution as @novinosrin but, if case is irrelevant, I'd suggest using FIND function rather than INDEX function.

 

data have;
  input abIc id1 bxI4D b_ID2 xyzID_3; 
  cards;
1 2 3 4 5
;run;

proc sql noprint;
  select name into :drop_list separated by ' '
  from sashelp.vcolumn
    where libname="WORK" and memname="HAVE" and find(name,'ID','i');
quit;

data want;
  set have;
  drop &drop_list;
run;

Art, CEO, AnalystFinder.com

 

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
  • 8 replies
  • 900 views
  • 4 likes
  • 4 in conversation