topic Re: Use data in column to create variable names in SAS Programming

Use data in column to create variable names

bmerry1 — Mon, 15 Apr 2013 15:57:08 GMT

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.

Re: Use data in column to create variable names

AncaTilea — Mon, 15 Apr 2013 16:09:28 GMT

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.

Re: Use data in column to create variable names

bmerry1 — Mon, 15 Apr 2013 16:16:00 GMT

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.

Re: Use data in column to create variable names

Tom — Mon, 15 Apr 2013 16:19:02 GMT

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;

Re: Use data in column to create variable names

bmerry1 — Mon, 15 Apr 2013 17:15:05 GMT

Thank you Anca and Tom. Both your examples are extremely helpful. I really appreciate your time.

Best,

Brian