"merge" numoerous tables but side by side in a new table

[MaxiHösi]

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

[LinusH]

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.

[MaxiHösi]

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]

---

@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 · Accepted solution]

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]

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

[MaxiHösi]

I tried your code and that's exactly what i wanted. Thank you very much 🙂

[ballardw]

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;