- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you Anca and Tom. Both your examples are extremely helpful. I really appreciate your time.
Best,
Brian