BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
MaxiHösi
Calcite | Level 5

Hello SAS-Forum,

 

I have problems merging numerous tables side by side in a new table.

I have the collumns car, value and period (for an example see below)

Car:              value:           period:

Renault       10.002          A

Toyota         12.000         A 

Hyundai      8.000           A

Renault       11.099           B

Toyota         12.054          B 

Hyundai      8.038            B

Jaguar        20.038           B

Renault       9.035             C

Toyota         10.154           C 

Hyundai      7.600            C

....                  ...                    ...

 

My goal is to write a new table that looks like that (in one table):

Car_A:              value_A:            Car_B:              value_B:               Car_C:                value_C:    ....    

Renault             10.002               Renault           11.099                   Renault              9.035

Toyota               12.000               Toyota             12.054                  Toyota              10.154

Hyundai             8.000                Hyundai          8.038                   Hyundai             7.600

                                                       Jaguar             20.038

 

I tried it with the following sas code:

%let period_1=a;
%let period_2=b;
%let period_3=c;
...

%macro test(start=, end=);
       %do i =start %to &end;
              data merge_horizontal;
                  set merge_horizontal;
                      if period= &&period_&i Then car_&&period_&i = car
                      if period= &&period_&i Then value_&&period_&i = value
run; %end; %mend test;
%test(start=1, end=3);

But it just creates me 2 new collumns (car_c and value_c), doesnt delete the column car and value and is not sorted.

 

I hope you understand what i want.

 

Thanks in advance

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Use a MERGE with no BY statement.

data want;
  merge have(rename=(car=carA value=valueA period=periodA) where=(periodA='A'))
        have(rename=(car=carB value=valueB period=periodB) where=(periodB='B'))
        have(rename=(car=carC value=valueC period=periodC) where=(periodC='C'))
 ;
 drop period: ;
run;

Result

Obs     carA      valueA     carB      valueB     carC      valueC

 1     Renault    10.002    Renault    11.099    Renault     9.035
 2     Toyota     12.000    Toyota     12.054    Toyota     10.154
 3     Hyundai     8.000    Hyundai     8.038    Hyundai     7.600
 4                  .       Jaguar     20.038                 .

But it might make more sense if the rows in the resulting table (the observations in the resulting dataset) are aligned by the CAR value.  In which case PROC TRANSPOSE would be much easier.

proc sort data=have;
  by car period;
run;
proc transpose data=have out=wide(drop=_name_) prefix=value;
  by car;
  var value;
  id period;
run;

Result

Obs      car      valueA    valueB    valueC

 1     Hyundai     8.000     8.038     7.600
 2     Jaguar       .       20.038      .
 3     Renault    10.002    11.099     9.035
 4     Toyota     12.000    12.054    10.154

 

View solution in original post

7 REPLIES 7
LinusH
Tourmaline | Level 20
Your desired output look more like a report than a dataset/data table.
Look into PROC REPORT or PROC TABULATE to create your desired layout.
Data never sleeps
MaxiHösi
Calcite | Level 5

What i found so far about proc report or proc tabulate is not really what i search. I want to continue working with the output.

Tom
Super User Tom
Super User

@MaxiHösi wrote:

What i found so far about proc report or proc tabulate is not really what i search. I want to continue working with the output.


You could not work with a DATASET that looks like that.

What kind of "work" are you planning to do with that report?

 

Tom
Super User Tom
Super User

Use a MERGE with no BY statement.

data want;
  merge have(rename=(car=carA value=valueA period=periodA) where=(periodA='A'))
        have(rename=(car=carB value=valueB period=periodB) where=(periodB='B'))
        have(rename=(car=carC value=valueC period=periodC) where=(periodC='C'))
 ;
 drop period: ;
run;

Result

Obs     carA      valueA     carB      valueB     carC      valueC

 1     Renault    10.002    Renault    11.099    Renault     9.035
 2     Toyota     12.000    Toyota     12.054    Toyota     10.154
 3     Hyundai     8.000    Hyundai     8.038    Hyundai     7.600
 4                  .       Jaguar     20.038                 .

But it might make more sense if the rows in the resulting table (the observations in the resulting dataset) are aligned by the CAR value.  In which case PROC TRANSPOSE would be much easier.

proc sort data=have;
  by car period;
run;
proc transpose data=have out=wide(drop=_name_) prefix=value;
  by car;
  var value;
  id period;
run;

Result

Obs      car      valueA    valueB    valueC

 1     Hyundai     8.000     8.038     7.600
 2     Jaguar       .       20.038      .
 3     Renault    10.002    11.099     9.035
 4     Toyota     12.000    12.054    10.154

 

MaxiHösi
Calcite | Level 5

i tried your solution and it works well. Thank you very much, thats exactly what i wanted 🙂

MaxiHösi
Calcite | Level 5
I tried your code and that's exactly what i wanted. Thank you very much 🙂
ballardw
Super User

I suggest providing a very clear example of what your next steps you will do with data in that "wide" format would actually be.

 

For example, I might expect to Merge BY car since you apparently want the same car on a single row. At which point repeated values of Car are next to pretty useless.

I almost suspect an attempt to mimic a spreadsheet method which a quite often a very poor way to use SAS as the tools are very different.

 

This  is the closest I would recommend to actually using. Please not providing your example data in the form of a data step is extremely helpful and may clarify questions (when done properly) that we may have to ask about your variable properties.

data have;
   input Car $    value    period $;
datalines;
Renault       10.002          A
Toyota         12.000         A 
Hyundai      8.000           A
Renault       11.099           B
Toyota         12.054          B 
Hyundai      8.038            B
Jaguar        20.038           B
Renault       9.035             C
Toyota         10.154           C 
Hyundai      7.600            C
;
proc sort data=have;
   by car period;
run;


Proc transpose data=have out=trans (drop=_name_)
   prefix=Value_;
   by car;
   id period;
   var value;
run;

   

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1099 views
  • 2 likes
  • 4 in conversation