Help for a simple summing problem?

[genemroz]

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
.
.
.
;

[FreelanceReinh · Accepted solution]

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;

[genemroz]

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

[PaigeMiller]

---

@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.

[genemroz]

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

Regards,
Gene

[FreelanceReinh]

---

@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.

[genemroz]

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

Gene

[Tom]

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;

[genemroz]

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

Thanks,

Gene

[PaigeMiller]

---

@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;

[genemroz]

Thanks for the prompt response and another excellent solution!

Gene

[Ksharp]

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;

[genemroz]

Thanks for this alternative!

Regards,

Gene