BookmarkSubscribeRSS Feed
Schwa
Fluorite | Level 6

Hello. I'm in the process of cleaning, merging, and harmonizing some very large data sets. Some of these sets have variables with the same variable names. What I'm looking for is a way to change all of the variable names in a dataset at once, without needing to know all of the variables ahead of time, or even how many there are. (As we often later decide we wanted to keep another variable from one of the sets, I would like to be able to, say, add a prefix or suffix to all of the variable names in a set.)

I've been searching the SAS help forums and files, but I think I don't know even what to search for here. Any help would be appreciated, even just a nudge in the right direction!

2 REPLIES 2
Reeza
Super User
*make fake data;
data sample;
do i=10000 to 12000;
	start_date=i;
	middle_date=i+3;
	end_date=i+5;
	date_no_change=start_date;
	output;
end;
format start_date end_date middle_date date9.;
run;

*create rename list;
proc sql noprint;
select catx("=", name, catt('DT_', tranwrd(upper(name), '_T1', ' '))) 
into :rename_list
separated by " "
from sashelp.vcolumn
where libname='WORK'
and memname='SAMPLE'
;
quit;

*check rename list in log;
%put &rename_list;

*if it works, apply to data set;
proc datasets library=work nodetails nolist;
modify sample;
rename &rename_list;
run; quit;

proc print data=sample noobs;
run;

This add DT as a prefix and T1 as a suffix to all variables. You may want to add a prefix though, it can make life easier since you can reference sets of variables easier.

 

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

 

ballardw
Super User

Caution: If the length of your existing variable plus any prefix/suffix exceeds 32 then there is potential for name truncation.

 

Caution: If you don't know the names of the variables you have the potential to accidentally create duplicate names across sets if you mix use of prefix and suffixes.

 

You may also want to investigate additional text in variable Labels where there is much more room without causing coding problems.

 

Something that might also be of interest for identifying like named variables. This creates a table of all the variables in all the data sets in a library. Replace "LIBRARY" with the name of your library. The value should be in caps as that is how it is stored in your metadata.

The format places an X to indicate membership in the data sets by column.

I include the type as a common issue is same named variable with different data types and that might indicate something else you want to look at.

 

proc format library=work;
value mark 
1='X'
other = ' '
;
run;

proc tabulate data=sashelp.vcolumn;
   where libname='LIBRARY' and memtype='DATA';
   class name type memname;
   table name*type,
         memname*n=' '*f=mark.
   ;
run;
         

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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