topic Re: sum by group in SAS Programming

sum by group

monday89 — Tue, 25 Feb 2020 17:12:09 GMT

I have the following table. What I would like to do is report the sum of all 1's overall by subjectID. Only 1's, no 2's, 0's, missing

have

SubjectID	value1	value2	value3
1	1	0
1	0	1	2
1	0	0	0
1	0	0	0
2	0	1	0
2	0	0	0
2	0	0	0
2	0	0	1
2	0	0	2
2	0	0	2
3	0	0	0
4	0	0	0
4	1	0	1
4	1	1	0

SubjectID	total
1	2
2	2
3	0
4	4

i tried the following

proc sql;

select subjectid, count(subjectid) as total

from a.have

where value1 = 1 or value2 = 1 or value3= 1;quit;

Re: sum by group

Krueger — Tue, 25 Feb 2020 17:41:55 GMT

Below will run according to what you requested; keep in mind if your expected output changes this likely won't cross over well.

Edit: Made correction to code.

proc sql;
create table want as
	select SubjectID, SUM(Value1=1)+SUM(Value2=1)+SUM(Value3=1) As Total
	from have
	group by SubjectID;
quit;

Re: sum by group

novinosrin — Tue, 25 Feb 2020 17:33:14 GMT

Hi @monday89 I'm afraid SQL is not best suited for wide datasets.

data have;
input SubjectID	value1	value2	value3;
cards;
1	1	0	. 
1	0	1	2
1	0	0	0
1	0	0	0
2	0	1	0
2	0	0	0
2	0	0	0
2	0	0	1
2	0	0	2
2	0	0	2
3	0	0	0
4	0	0	0
4	1	0	1
4	1	1	0
;
data want;
 do until(last.subjectid);
  set have;
  by subjectid;
  array t value1-value3;
  call missing(n);
  do over t;
   if t=1 then n=sum(t,n);
  end;
  sum=sum(sum,n,0);
 end;
 keep subjectid sum;
run;