BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
NewUsrStat
Quartz | Level 8

Is there a way to remove columns that does not end with "_22" from a data set? Moreover, is there a way to remove "_22" suffix from column names from another data set?

Thank you in advance

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User
proc sql noprint;
select catx("=",name,substr(name,1,length(name)-3)), substr(name,1,length(name)-3)
into :renamestr separated by " ", dropstr separated by " " from dictionary.columns where libname = "WORK" and memname = "HAVE" and substr(name,length(name)-2) = "_22"; quit; data want; set have; drop &dropstr.; rename &renamestr.; run;

Since you want to remove variables, PROC DATASETS can't do it, a DATA step is needed. My bad.

View solution in original post

5 REPLIES 5
Kurt_Bremser
Super User

In PROC SQL, you can retriev variable names from DICTIONARY.COLUMNS. With SELECT INTO, you can create macro variables which contain the necessary lists for DROP and RENAME statements used in PROC DATASETS.

NewUsrStat
Quartz | Level 8
Thank you kurt_Bremser. Is there an example or something like that to inspire to?
Kurt_Bremser
Super User
proc sql noprint;
select catx("=",name,substr(name,1,length(name)-3)), substr(name,1,length(name)-3)
into :renamestr separated by " ", dropstr separated by " " from dictionary.columns where libname = "WORK" and memname = "HAVE" and substr(name,length(name)-2) = "_22"; quit; data want; set have; drop &dropstr.; rename &renamestr.; run;

Since you want to remove variables, PROC DATASETS can't do it, a DATA step is needed. My bad.

Astounding
Opal | Level 21

What ways do you know (if any) to get the names of variables in your data set?

ballardw
Super User

In my occasionally humble opinion, the question is why to do you have variables with names you don't want in the first place. Especially something ending in _22. That almost implies that you have 21 other names that are acceptable (ending in _1  _2 _3 ... _21).

 

When you have data set names you don't like and want different names then Proc Datasets will let you rename the variables in place.

Proc datasets library=Somelib;
   modify adataset;
      rename var_22 = var
             thatvar_22 = thatvar
       ;
run;
quit;

The example code will modify the dataset named Adataset in the library named Somelib by renaming tow variables.

Note this procedure uses a QUIT statement to end the procedure.

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

From SAS Users blog
Want more? Visit our blog for more articles like these.
5 Steps to Your First Analytics Project Using SAS

For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 211 views
  • 1 like
  • 4 in conversation