topic Re: Add new row with the sum of specific rows in SAS Programming

Add new row with the sum of specific rows

Konkordanz — Wed, 22 Sep 2021 13:52:49 GMT

Hi,

I have >5.000 rows and 130 columns.

Column 1: Postal codes (inkl.duplicates)

Column 2-129 with €-values (names of columns: sp1-sp129)

What I need is a new row with the result of the sum of all rows for each numeric column (sp1-sp129), if the postal code is XXXXX. How can I do this with a short sql-code? Thank you for help!

For example:

postal_code	sp1	sp2	spN
11111	65	78	...
11111	12	65	...
22222	87	21	...
33333	42	45	...
22222	65	87	...
11111	24	86	...
1111_sum	101	229	...

Re: Add new row with the sum of specific rows

PaigeMiller — Wed, 22 Sep 2021 14:33:24 GMT

I wouldn't even attempt this in PROC SQL

proc summary data=have nway;
    class postal_code;
    var sp1-sp129;
    output out=_sums_ sum=;
run;

proc sort data=have;
    by postal_code;
run;

data want;
    length postal_code $ 10;
    set have _sums_;
    by postal_code;
    if _type_=1 then postal_code=cats(postal_code,'_sum');
run;

Re: Add new row with the sum of specific rows

Reeza — Wed, 22 Sep 2021 14:44:32 GMT

How do you include duplicates with the different values but then sum them up if the postal code is XXX? Those are contradictory requirements.

Do you mean you only want postal codes with the same characters added up, ie with the pattern of letters and just the total at the bottom? Do you expect to see a line of 22222_sum as well?

@Konkordanz wrote:

Hi,

I have >5.000 rows and 130 columns.

Column 1: Postal codes (inkl.duplicates)

Column 2-129 with €-values (names of columns: sp1-sp129)

What I need is a new row with the result of the sum of all rows for each numeric column (sp1-sp129), if the postal code is XXXXX. How can I do this with a short sql-code? Thank you for help!

For example:

postal_code sp1 sp2 spN

11111 65 78 ...

11111 12 65 ...

22222 87 21 ...

33333 42 45 ...

22222 65 87 ...

11111 24 86 ...

1111_sum 101 229 ...

Re: Add new row with the sum of specific rows

Konkordanz — Thu, 23 Sep 2021 08:22:43 GMT

Thank for response and sry for inaccurate description!
Well, I actually dont want to sum all postal-codes. I mean...it would be okay, afterwards I would just filter away the lines i doesnt need. But actually I want to sum certain codes; in my example the 11111. So I need a way to summarize the certain data with a filter and add the result as a new row into my dataset. Do you have a short solution? thanks!

Re: Add new row with the sum of specific rows

Konkordanz — Thu, 23 Sep 2021 08:24:57 GMT

Thank for response, is working...but: actually I dont want to sum all postal-codes But only certain codes; in my example the 11111. So I need a way to summarize the certain data with a filter and add the result as a new row into my dataset. Do you have a short solution? thanks!

Re: Add new row with the sum of specific rows

Patrick — Thu, 23 Sep 2021 09:49:38 GMT

It's often useful to show the desired result based on the sample data (ideally provided via a SAS data step). Below 3 want options for what you might ask for. Check if any of the options fits your requirement or else let us know what comes closest and what's still missing.

As for a total line: That's then more about creating a report than a new table. So if you need a report then please tell us - and SHOW us how this report would need to look like.

data have;
  input postal_code $ sp1 sp2;
datalines;
11111 65 78
11111 12 65
22222 87 21
33333 42 45
22222 65 87
11111 24 86
;

data want_1;
  set have;
  spN=sum(of sp:);
run;
data want_2;
  set have;
  if postal_code='11111' then
    spN=sum(of sp:);
run;
data want_3;
  set have;
  if postal_code='11111';
  spN=sum(of sp:);
run;

Re: Add new row with the sum of specific rows

Konkordanz — Thu, 23 Sep 2021 10:29:43 GMT

double-post: I got it! I just put the Where-command into the summary-command, built a table per sum-group and combined all tables afterwards.

Thanks!