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
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
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.
@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?
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
i tried your solution and it works well. Thank you very much, thats exactly what i wanted 🙂
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;
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!
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.
Ready to level-up your skills? Choose your own adventure.