Hi,
I apologize if this is a basic question, I am just a little stuck on discovering the best method of doing this. Is it possible to pull information from a column in one dataset to generate variable names in a second dataset?
For example
Have:
Dataset1:
ID Make
1 Ford
2 GM
3 Mercedes
4 Toyota
5 Honda
Dataset2:
Store var1 var2 var3 var4 var5
100 10 4 0 0 0
101 0 0 5 0 0
102 1 0 4 5 2
Want:
Store Ford GM Mercedes Toyota Honda
100 10 4 0 0 0
101 0 0 5 0 0
102 1 0 4 5 2
Thank you in advance for your time and consideration.
Hi,
Do you actually want to rename your columns (currently labeled:var1 -- var5) as the make of a car(Ford, GM...)?
Do you have an ID in dataset2?;
If that's what you want then;
proc transpose data = dataset1;id make;var id;run;
proc sql;
select catt(" =' ",make," ' "),
compress(put(count(unique id), 2.0))
into: labels separated by ' , ',: max_num
from dataset1;
quit;
%put &labels. &max_num.;
data want;
set dataset2;
%macro label();
%do i = 1 %to &max_Num.;
label var&i. %scan("&labels.", &i, ",");
%end;
%mend;
%label();
run;
Let me know.
Hi,
Do you actually want to rename your columns (currently labeled:var1 -- var5) as the make of a car(Ford, GM...)?
Do you have an ID in dataset2?;
If that's what you want then;
proc transpose data = dataset1;id make;var id;run;
proc sql;
select catt(" =' ",make," ' "),
compress(put(count(unique id), 2.0))
into: labels separated by ' , ',: max_num
from dataset1;
quit;
%put &labels. &max_num.;
data want;
set dataset2;
%macro label();
%do i = 1 %to &max_Num.;
label var&i. %scan("&labels.", &i, ",");
%end;
%mend;
%label();
run;
Let me know.
I would like to rename var1 -- var5 as Ford, GM, etc...
I do not currently have an ID linking both datasets, although I could if necessary.
Thank you.
Looks like you want to use the ID value in DATASET1 to generate the old variable name?
Pretty easy to do using PROC SQL to generate the list.
proc sql noprint ;
select cats('VAR',id,'=',make)
into :renames separated by ' '
from dataset1
;
quit;
data want ;
set dataset2;
rename &renames ;
run;
If the list is so long that the renaming will not fit into a macro variable then you could also just use a SAS dataset to write the code to file.
filename renames temp;
data _null_;
set dataset1 end=eof;
file rename ;
if _n_=1 then put 'rename' ;
put 'var' id '=' make ;
if eof then put ';' ;
run;
data want ;
set dataset2;
%inc renames / source2 ;
run;
Thank you Anca and Tom. Both your examples are extremely helpful. I really appreciate your time.
Best,
Brian
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.