DATA Step, Macro, Functions and more

Use data in column to create variable names

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Use data in column to create variable names

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
Solution
‎04-15-2013 12:09 PM
Super Contributor
Posts: 543

Re: Use data in column to create variable names

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.

Smiley Happy

View solution in original post


All Replies
Solution
‎04-15-2013 12:09 PM
Super Contributor
Posts: 543

Re: Use data in column to create variable names

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.

Smiley Happy

Occasional Contributor
Posts: 13

Re: Use data in column to create variable names

Posted in reply to AncaTilea

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.

Super User
Super User
Posts: 7,042

Re: Use data in column to create variable names

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;

Occasional Contributor
Posts: 13

Re: Use data in column to create variable names

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

Best,

Brian

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 253 views
  • 3 likes
  • 3 in conversation