- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I have two tables :
Table 1:Rows
col1 | col2 | col3 | col4 | col5 |
data | data | data | data | data |
data | data | data | data | data |
Table 2:Cols (Only that one row), if needed I can make these values the column names in this table with no rows
1 | 2 | 3 | 4 | 5 |
col_name1 | col_name2 | col_name3 | col_name4 | col_name5 |
How can I rename the columns in Table1:Rows with the values in Table2:Cols (they're guaranteed to be in the same order) to get:
col_name1 | col_name2 | col_name3 | col_name4 | col_name5 |
data | data | data | data | data |
data | data | data | data | data |
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Is Table2 supposed to be a SAS dataset? If so, what are the actual variable names. By default SAS will not create or allow use of variable names that start with digits.
Unless you have hundreds of these and proper SAS data sets this is likely easiest with Proc Datatsets.
Proc datasets library=work (or the name of the library the data sets are in) nodetail; modify table1; /* or what ever the real name may be*/ rename col1 = col_name1 col2 = col_name2 col3 = col_name3 col4 = col_name4 /* repeat as needed*/ ; run; quit;
It is a good idea to learn Proc Datasets as it is a very flexible tool for changing names, variable labels and formats and a few other things related to managing data set properties. The procedure modifies the data in place and does not actually access records so is very time efficient for large data sets.
Names without actual data types restricts most approaches that may have come to mind from your "(they're guaranteed to be in the same order)". Variables have types in SAS, even if only two. And if you have a "col1" that contains text data and an example dataset (table2) where col_name2 is numeric then you can't append table 1 data to table2 names. Type has to align exactly. Second is length. If you create a table2 for a character variable with length 8 and try to stuff 80 characters from table1 into that column then 72 characters get ignored.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
First match the two lists.
proc transpose data=table1 (obs=0) out=old_names name=old_name ;
var _all_;
run;
proc transpose data=table2 out=new_names ;
var _all_;
run;
data names ;
set old_names;
set new_names;
run;
Once you have them matched up then use them to generate a RENAME statement.
filename code temp;
data _null_:
set names end=eof;
file code;
if _n_=1 then put 'rename' ;
length pair $200;
pair = catx('=',nliteral(old_name),nliteral(col1));
put @3 pair ;
if eof then put ';';
run;
Once you have the RENAME statement you can use it to rename the variables.
If the dataset is small just make a new dataset.
data want;
set table1;
%include code / source2;
run;
If the dataset is large you might want to use PROC DATASETS to modify the names in place.
proc datasets nolist lib=WORK ;
modify TABLE1 ;
%include code / source2;
run;
quit;
Note that the first step of matching the old name to the new name would be easier if the variables in TABLE2 had the same names as the corresponding variables in TABLE1.
proc transpose data=table2 out=names name=old_name;
var _all_ ;
run;