topic Re: Help for a simple summing problem? in SAS Programming

Help for a simple summing problem?

genemroz — Sun, 25 Apr 2021 14:56:04 GMT

Esteemed Advisers:

I suspect this is a simple problem in summing but the solution is eluding me:

I need help with SAS code that sums k(binary) for Types A1, B1 and C1 by ID and outputs as ID, D1,sumk and similarly summing for Types A2,B2,C2 to produce ID, D2, sumk , then summing for Types A3,B3, C3 to produce ID,D3,sumk and so forth. See below:

Data Have;
input ID Type $ K;

datalines;
1 A1 0
1 A2 0
1 A3 1
1 B1 1
1 B2 0
1 B3 0
1 C1 0
1 C2 0
1 C3 0
2 A1 0
2 A2 0
2 A3 1
2 B1 0
2 B2 0
2 B3 0
2 C1 0
2 C2 1
2 C3 0
.
.
.
;

data want;
ID Type $ sumk;
1 D1 1
1 D2 0
1 D3 1
2 D1 0
2 D2 1
2 D3 1
.
.
.
;

Re: Help for a simple summing problem?

FreelanceReinh — Sun, 25 Apr 2021 15:12:02 GMT

Hello @genemroz,

Try PROC SQL:

proc sql;
create table want as
select id, cats('D',substr(type,2)) as type length=8, sum(k) as sumk
from have
group by 1,2
order by 1,2;
quit;

Re: Help for a simple summing problem?

Tom — Sun, 25 Apr 2021 15:51:44 GMT

Do you need a dataset? Or just a report?

proc format ;
value $d 
  'A1','B1','C1'='D1'
  'A2','B2','C2'='D2'
  'A3','B3','C3'='D3'
;
run;

proc report data=have ;
 column id type k ;
 define id / group ;
 define type / group format=$d. ;
 define k / sum;
run;

Re: Help for a simple summing problem?

genemroz — Sun, 25 Apr 2021 16:00:53 GMT

Thank you for the prompt response and excellent solution using PROC SQL. However, I am curious to know if the same result can be obtained via a SAS Data Step?
Thanks again...

Gene

Re: Help for a simple summing problem?

genemroz — Sun, 25 Apr 2021 16:02:02 GMT

I need the result as a dataset, not a report.

Thanks,

Gene

Re: Help for a simple summing problem?

PaigeMiller — Sun, 25 Apr 2021 16:22:06 GMT

@genemroz wrote:
I need the result as a dataset, not a report.

proc format ;
value $d 
  'A1','B1','C1'='D1'
  'A2','B2','C2'='D2'
  'A3','B3','C3'='D3'
;
run;

proc summary nway data=have;
    class id type;
    var k;
    format type $d.;
    output out=want sum=;
run;

Re: Help for a simple summing problem?

PaigeMiller — Sun, 25 Apr 2021 16:26:18 GMT

@genemroz wrote:
Thank you for the prompt response and excellent solution using PROC SQL. However, I am curious to know if the same result can be obtained via a SAS Data Step?

You shouldn't write your own DATA step code to sum by groups. SAS has already put in the effort to provide ways to sum by groups (PROC SUMMARY, PROC MEANS, PROC SQL) and has tested this code, debugged the code, and this code has been proven in a bazillion real-world applications. You (or your company or university) is paying SAS for this code, use it! Don't spend your time to re-invent the wheel.

We see many many many times when people try to write their own data step code to do this, they either get it wrong, or spend huge amounts of time on it, neither of which is a good outcome.

Re: Help for a simple summing problem?

FreelanceReinh — Sun, 25 Apr 2021 16:33:26 GMT

@genemroz wrote:
Thank you for the prompt response and excellent solution using PROC SQL. However, I am curious to know if the same result can be obtained via a SAS Data Step?
Thanks again...

Gene

You're welcome.

Here's a DATA step solution using an array:

data want(drop=i k);
array d[3] _temporary_;
do until(last.id);
  set have;
  by id;
  d[input(substr(type,2),32.)]+k;
end;
do i=1 to dim(d);
  type=cats('D',i);
  sumk=d[i];
  output;
end;
call missing(of d[*]);
run;

This assumes that HAVE is sorted (or indexed) by ID.

Re: Help for a simple summing problem?

genemroz — Sun, 25 Apr 2021 16:37:55 GMT

Thanks for the prompt response and another excellent solution!

Gene

Re: Help for a simple summing problem?

genemroz — Sun, 25 Apr 2021 16:42:56 GMT

Thanks for this. I look forward to trying it out on my dataset.

Gene

Re: Help for a simple summing problem?

genemroz — Sun, 25 Apr 2021 16:45:08 GMT

Thanks for your wise comments. Having already spent too much time on this before posting the question this morning, I completely understand.

Regards,
Gene

Re: Help for a simple summing problem?

Ksharp — Mon, 26 Apr 2021 12:44:46 GMT

Just for fun.

Data Have;
input ID Type $ K;
datalines;
1 A1 0
1 A2 0
1 A3 1
1 B1 1
1 B2 0
1 B3 0
1 C1 0
1 C2 0
1 C3 0
2 A1 0
2 A2 0
2 A3 1
2 B1 0
2 B2 0
2 B3 0
2 C1 0
2 C2 1
2 C3 0
;

proc sql;
create table want as
select id,'D1' as type,sum(k) as sum from have where type in ('A1','B1','C1') group by id 
union
select id,'D2' as type,sum(k) as sum from have where type in ('A2','B2','C2') group by id 
union
select id,'D3' as type,sum(k) as sum from have where type in ('A3','B3','C3') group by id 
;
quit;

Re: Help for a simple summing problem?

genemroz — Mon, 26 Apr 2021 14:48:22 GMT

Thanks for this alternative!

Regards,

Gene