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

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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