topic Re: "merge" numoerous tables but side by side in a new table in SAS Programming

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

MaxiHösi — Thu, 21 Mar 2024 09:52:12 GMT

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

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

LinusH — Thu, 21 Mar 2024 10:05:30 GMT

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.

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

MaxiHösi — Thu, 21 Mar 2024 14:21:06 GMT

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.

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

Tom — Thu, 21 Mar 2024 14:55:00 GMT

@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?

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

Tom — Thu, 21 Mar 2024 15:08:44 GMT

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

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

ballardw — Thu, 21 Mar 2024 15:14:53 GMT

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;

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

MaxiHösi — Mon, 25 Mar 2024 18:03:47 GMT

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

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

MaxiHösi — Mon, 25 Mar 2024 18:05:15 GMT

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