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

I have a dataset where all the variables end in "_c" and I'd like to now remove the suffix.

However, I can't seem to find a way to do so.  I found some code below using the scan feature, but it doesn't seem to work.

Does anyone have an easy way to remove a suffix for all variables in a dataset?

 

data contamination_c;
input id_c name_c :$10. age_c gender_c diagnosis1_c diagnosis2_c;
datalines;
112 Albert 10 85 90 89
223 Joe 11 99 98 91
323 Jim 12 100 100 100
414 Sally 11 78 89 100
;
run;

proc sql noprint;
select cats(name,'=',scan(name, 1, '_'))
into :suffixlist
separated by ' '
from dictionary.columns
where libname = 'WORK' and memname = 'contamination_c' and 'c' = scan(name, 2, '_');
quit;
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

That code is pretty close, you need to make sure you case matches for the memname and name to generate the rename statement.

 


proc sql noprint;
select cats(name,'=',scan(name, 1, '_'))
into :suffixlist
separated by ' '
from dictionary.columns
where libname = 'WORK' and upper(memname) = 'CONTAMINATION_C' and 'C' = scan(upper(name), 2, '_');
quit;

%put &suffixlist.;

data want;
set contamination_c;
rename &suffixlist.;
run;

View solution in original post

5 REPLIES 5
Astounding
PROC Star

In dictionary.columns, MEMNAME is stored in uppercase.  Just change to:

 

... and memname = 'CONTAMINATION_C' and ...

Reeza
Super User

That code is pretty close, you need to make sure you case matches for the memname and name to generate the rename statement.

 


proc sql noprint;
select cats(name,'=',scan(name, 1, '_'))
into :suffixlist
separated by ' '
from dictionary.columns
where libname = 'WORK' and upper(memname) = 'CONTAMINATION_C' and 'C' = scan(upper(name), 2, '_');
quit;

%put &suffixlist.;

data want;
set contamination_c;
rename &suffixlist.;
run;
einstein
Quartz | Level 8

It almost worked!  All the variables changed except for variables with multiple underscores.

I have some variables with multiple underscores like not_enrolled_o and those variables for some reason are not dropping the "_o".

 

Is there something else I need to add to the code to get it to recognize those variables with multiple underscores?

 

Reeza
Super User

Then your SQL code needs to change. Take a look at how you're determining the variables to include.

 

 

einstein
Quartz | Level 8

Ah, got it!  Thank you so much!

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
  • 5 replies
  • 10068 views
  • 2 likes
  • 3 in conversation