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

I need to add a prefix to a selection of my column names. All the select column names start with _ then are a zip code (i.e., _90001). 

I want to add a prefix to these, in from of the _

 

There are nearly 2000 columns like this, so manually coding this is not really a functional option.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
data have;
input _001-_004;
cards;
1 2 3 4
;
%let prefix=mean ;
proc transpose data=have(obs=0) out=vname;
var _all_;
run;
data vname;
 set vname(where=(_name_ =:'_'));
 new_name=cats("&prefix.",_name_);
run;
proc sql noprint;
select catx('=',_name_,new_name) into :rename separated by ' ' from vname;
quit;
proc datasets library=work nolist nodetails;
modify have;
rename &rename.;
quit;

View solution in original post

7 REPLIES 7
Ksharp
Super User
data have;
input _001-_004;
cards;
1 2 3 4
;
%let prefix=mean ;
proc transpose data=have(obs=0) out=vname;
var _all_;
run;
data vname;
 set vname(where=(_name_ =:'_'));
 new_name=cats("&prefix.",_name_);
run;
proc sql noprint;
select catx('=',_name_,new_name) into :rename separated by ' ' from vname;
quit;
proc datasets library=work nolist nodetails;
modify have;
rename &rename.;
quit;
Cal_Hottie
Fluorite | Level 6
This worked, thanks!
andreas_lds
Jade | Level 19

You should think about transposing the data so that your variable names don't contain information that should be stored in an own variable. Almost always a long data structure beats a wide one.

Cal_Hottie
Fluorite | Level 6
I agree that long is the way to go. Am trying to get there. This is one of 2 data sets. the other set is long, with dates. I think ultimately, I want to output each zip into own table, process independently, then merge back. But not yet sure how to call the separate tables to have the zip in the name. Will make a separate post.
ballardw
Super User

@Cal_Hottie wrote:
I agree that long is the way to go. Am trying to get there. This is one of 2 data sets. the other set is long, with dates. I think ultimately, I want to output each zip into own table, process independently, then merge back. But not yet sure how to call the separate tables to have the zip in the name. Will make a separate post.

Multiple data sets typically adds a great deal of often not needed complication.

 

"Merge back" based on what criteria?

 

If you provide small example data sets of both this "zip codes as columns", the other data set and what the result is supposed to be we may be able to save you a lot of headaches. Example data in the form of data steps that behaves similarly is a good idea in general.

 

Your example likely only needs to have 3 to 5 of these "zip" columns plus other variables needed for the next step. The example of the "other data" should be such that it works with the 3 to 5 column example.

 

 

mkeintz
PROC Star

You can access the needed metadata (such as variable names - as opposed to variable values) with PROC SQL access to dictionary resources.  SQL can also be used to build a macro variable with the set of desired renames applied to the variables in question.  Then, as per per @Ksharp 's response, just apply PROC DATASETS using the macro variable listing the renames.

 

data have;
input _001 - _004;
cards;
1 2 3 4
run;


%let prefix=mean ;

proc sql noprint ;
  select cats(name,"=&prefix",name) into :rename_list separated by ' '
  from dictionary.columns
  where libname='WORK' and memname='HAVE' and name like '_%';
quit;
%put &=rename_list;

proc datasets library=work nolist nodetails;
  modify have;
  rename &rename_list;
quit;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Tom
Super User Tom
Super User

To change the name of a variable use the RENAME statement or RENAME= dataset option.

If the number of variables is small you could build the OLD=NEW name pairs into a macro variable.  But since macro variables are limited to 64K bytes doing that for 2000+ variables will not work.  So instead just write the code to a file.

 

First get the existing names.  You could use PROC CONTENTS or reference the DICTIONARY.COLUMNS metadata table in SQL code (or the SASHELP.VCOLUMN view to the same in normal code).  But sometimes I find it easier to use PROC TRANSPOSE to get the names.  Use the OBS=0 dataset option so it just generates the list of names.

 

So if your existing dataset is named HAVE and you want to generate a new dataset named WANT where all of the _XXXX variables are renamed to HAVE_XXXX instead you could do something like this:

First generate NAMES dataset with all of the variable names.  Then generate a temporary text file referenced via the fileref CODE using a data step.  Then run a data step to make WANT by reading in HAVE and using %INCLUDE to add the generated RENAME statement to the step.

proc transpose data=have(obs=0) out=names;
  var _: ;
run;
filename code temp;
data _null_;
  set names end=eof;
  file code;
  if _n_=1 then put 'rename';
  put _name_ '= HAVE' _name_;
 if eof then put ';' ;
run;
data want;
  set have;
%include code;
run;

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1737 views
  • 3 likes
  • 6 in conversation