Hi, I am attempting to rename 417 columns labeled COL1-COL417 using names from a separate data set. (see the data set below)
I have a separate data set with one column that has all 417 names that I would like to use to replace COL1-COL417. What is an efficient way to approach this?
Thank you!
You might take advantage of the SQL UNION operator which will append a table by column position rather than name.
Base SAS(R) 9.2 Procedures Guide
/* get column names */
Proc SQL NoPrint ;
Select names
Into :colnames
Separated by ' '
From namelist
;
Quit ;
/* create empty table */
Data temp ;
Length Industry_IO_Code $ 8
&colnames 8
;
Run ;
/* append data */
Proc SQL ;
Create table want as
Select * from temp (obs = 0)
Union
Select * from have
;
Quit ;
(Untested code)
Richard
Message was edited by: Richard Carson Apologies: Forgot the From clause in the first SQL
Depends how automated you want it.
One quick way, is in the excel sheet create a concatenate formula in the third column that creates the field as follows:
=concatenate(A1, " = ", B1)
Col1=New_Name1
Then copy and paste that into a code window as follows:
Data rename;
set have;
rename
*Paste the excel formula output here;
;
run;
Thanks for the quick response.
I would like to automate this process because it is something that I will be running for data sets with varying numbers of columns. Therefore, taking myself out of the program would be ideal.
If I understand what you are trying to do, you might be able to use something like the following:
/*create some test data*/
data have;
input iocode $ col1 col2 col3;
cards;
1111A0 1 2 3
1111B0 4 5 6
111200 7 8 9
;
/*add record number to the data*/
data need;
set have;
recnum=_n_;
run;
proc sql noprint;
select "col"||strip(put(recnum,3.))||"=_"||strip(iocode)
into :renames separated by " "
from need
;
quit;
data want;
set have (rename=(&renames.));
run;
You might take advantage of the SQL UNION operator which will append a table by column position rather than name.
Base SAS(R) 9.2 Procedures Guide
/* get column names */
Proc SQL NoPrint ;
Select names
Into :colnames
Separated by ' '
From namelist
;
Quit ;
/* create empty table */
Data temp ;
Length Industry_IO_Code $ 8
&colnames 8
;
Run ;
/* append data */
Proc SQL ;
Create table want as
Select * from temp (obs = 0)
Union
Select * from have
;
Quit ;
(Untested code)
Richard
Message was edited by: Richard Carson Apologies: Forgot the From clause in the first SQL
Thanks, Richard. This did exactly what I needed.
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.
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.