Create new data set

[Haydo]

I have dataset (2 Million rows) with the following varibales:

• Suburb
• Suburb_Latitude
• Suburb_longitude
• Group (Group1, Group2)
• SubGroup(Subrgroup1, Subrgroup2,Subrgroup3,Subrgroup4,Subrgroup5,Subrgroup6)
• Offered (Y,N)
• Count (numerical)

For the values I have used mock entries.

The Request.

I'd like 1 line per Suburb summing the Count Variable as per the table below.

[Kurt_Bremser]

Please do not post pictures of data; supply example data in a self-contained DATA step with DATALINES.

For summations like you describe, PROC MEANS/SUMMARY is the right tool.

[Haydo]

Suburb	Group1	Group2	Subrgroup1	Subrgroup2	Subrgroup3	Subrgroup4	Subrgroup5	Subrgroup6	Offered Y	Offered N
Suburb1	3322	2282	321	987	934	865	686	1811	2800	2804
Suburb2	4468	2084	1045	1270	1047	1300	466	466	3270	3282
Suburb3	4487	889	1046	250	753	931	1896	1896	2680	2696

[Haydo]

Apologies not sure how to add, DATA step with DATALINES. I've added a table with the desired outcome and I hope this helps.

[Tom · Accepted solution]

Writing a DATA step is easier than getting all of those lines to appear on the page.

data want;
  input Suburb :$10. Group1 Group2 Subrgroup1-Subrgroup6 OfferedY OfferedN;
cards;
Suburb1 3322 2282 321 987 934 865 686 1811 2800 2804
Suburb2 4468 2084 1045 1270 1047 1300 466 466 3270 3282
Suburb3 4487 889 1046 250 753 931 1896 1896 2680 2696
;

If your actual data has variables with those names then a simple proc summary will work:

proc summary data=have nway;
  class suburb;
  var Group1 Group2 Subrgroup1-Subrgroup6 OfferedY OfferedN;
  output out=want sum= ;
run;

But your written description of the source made it sound more complicated. Please share a similar data step to create a small example of the source data.

[Haydo]

Mock up of data

Suburb	Group1	Group2	Subrgroup1	Subrgroup2	Subrgroup3	Subrgroup4	Subrgroup5	Subrgroup6	Offered Y	Offered N
Suburb1	2159	1483	209	642	607	562	446	1177	1820	1823
Suburb1	1163	799	112	345	327	303	240	634	980	981
Suburb2	2904	1355	679	826	681	845	303	303	2126	2133
Suburb2	1564	729	366	445	366	455	163	163	1145	1149
Suburb3	1570	311	366	88	264	326	664	664	938	944
Suburb3	2917	578	680	163	489	605	1232	1232	1742	1752

Desired Outcome

Suburb	Group1	Group2	Subrgroup1	Subrgroup2	Subrgroup3	Subrgroup4	Subrgroup5	Subrgroup6	Offered Y	Offered N
Suburb1	3322	2282	321	987	934	865	686	1811	2800	2804
Suburb2	4468	2084	1045	1270	1047	1300	466	466	3270	3282
Suburb3	4487	889	1046	250	753	931	1896	1896	2680	2696