topic Re: Combine Rows without replacing exisiting records in SAS Programming

Combine Rows without replacing exisiting records

Belle — Fri, 05 Apr 2019 20:58:50 GMT

Hello,

I need to combine rows by certain groups, and keep everything into one row. I tried proc means, but it replaced previous records.

Also is there a way to combine the field names? Please see the highlights in the final result (ie: aa_MM1).

Thanks in advance.

Here is original data:

data test;
input locations $ year type $ MM1-MM4;
datalines;
NY 2017 aa 560 560 687 560
NY 2017 bb 785 432 450 222
NY 2017 cc 160 160 765 160
NY 2018 aa 360 360 360 360
NY 2018 bb 570 570 493 570
NY 2018 cc 160 245 542 160
NY 2019 bb 432 340 360 532
NY 2019 cc 555 457 675 988
PA 2017 aa 560 560 687 560
PA 2017 bb 785 432 450 222
PA 2018 aa 360 360 360 360
PA 2018 bb 570 570 493 570
PA 2018 cc 160 245 542 160
PA 2019 bb 432 340 360 532
;

locations	year	type	MM1	MM2	MM3	MM4
NY	2017	aa	560	560	687	560
NY	2017	bb	785	432	450	222
NY	2017	cc	160	160	765	160
NY	2018	aa	360	360	360	360
NY	2018	bb	570	570	493	570
NY	2018	cc	160	245	542	160
NY	2019	bb	432	340	360	532
NY	2019	cc	555	457	675	988
PA	2017	aa	560	560	687	560
PA	2017	bb	785	432	450	222
PA	2018	aa	360	360	360	360
PA	2018	bb	570	570	493	570
PA	2018	cc	160	245	542	160
PA	2019	bb	432	340	360	532

Below is what I want to be:

locations	year	aa_MM1	aa_MM2	aa_MM3	aa_MM4	bb_MM1	bb_MM2	bb_MM3	bb_MM4	cc_MM1	cc_MM2	cc_MM3	cc_MM4
NY	2017	560	560	687	560	785	432	450	222	160	160	765	160
NY	2018	360	360	360	360	570	570	493	570	160	245	542	160
NY	2019					432	340	360	532	555	457	675	988
PA	2017	560	560	687	560	785	432	450	222
PA	2018	360	360	360	360	570	570	493	570	160	245	542	160
PA	2019					432	340	360	532

Re: Combine Rows without replacing exisiting records

PaigeMiller — Fri, 05 Apr 2019 21:55:36 GMT

Unless this is the output needed for some sort of report,if it is data that will be analyzed, you are better off not doing this and leaving the data in the original format. It is much easier in SAS to do almost any analysis on data in the first layout than data in the second layout.

Re: Combine Rows without replacing exisiting records

Tom — Fri, 05 Apr 2019 22:10:08 GMT

Just transpose it twice.

proc transpose data=test out=middle;
  by locations year type;
  var mm1-mm4;
run;
proc transpose data=middle out=want(drop=_name_) delim=_;
  by locations year ;
  var col1 ;
  id type _name_;
run;

Re: Combine Rows without replacing exisiting records

Belle — Sat, 06 Apr 2019 01:37:31 GMT

Hi PaigeMiller,

This is for the final report, not the analysis.

Thanks

Re: Combine Rows without replacing exisiting records

Belle — Sat, 06 Apr 2019 01:38:18 GMT

Hi Tom,

This works. Thank you very much.