BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bmerry1
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
AncaTilea
Pyrite | Level 9

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

4 REPLIES 4
AncaTilea
Pyrite | Level 9

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

bmerry1
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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;

bmerry1
Calcite | Level 5

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

Best,

Brian

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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