CODEWITHSUNDEEP
sas
Jebediah15
Jebediah15

Reputation: 794

Sum Vertically for a By Condition

I checked out this previous post (LINK) for potential solution, but still not working. I want to sum across rows using the ID as the common identifier. The num variable is constant. The id and comp the two variables I want to use to creat a pct variable, which = sum of [comp = 1] / num

Have:

id  Comp    Num
1   1       2
2   0       3
3   1       1
2   1       3
1   1       2
2   1       3

Want:

id  tot  pct
1   2    100
2   3    0.666666667
3   1    100

Currently have:

proc sort data=have;
    by id;
run;

data want;
    retain tot 0;
  set have;
    by id;
        if first.id then do;
            tot = 0;
            end;
        if comp in (1) then tot + 1;
            else tot + 0;
        if last.id;
            pct = tot / num;            
        keep id tot pct; 
        output;
run;

Upvotes: 1

Views: 3351

Answers (2)

criticalth
criticalth

Reputation: 438

Hi there is a much more elegant solution to your problem :)

proc sort data = have;
    by id;
run;

data want; 
    do _n_ = 1 by 1 until (last.id);
        set have ; 
        by id ; 
        tot = sum (tot, comp) ; 
    end ; 
    pct = tot / num ; 
run;

I hope it is clear. I use sql too because I am new and the DOW loop is rather complicated but in your case its pretty straightforward.

Upvotes: 0

DomPazz
DomPazz

Reputation: 12465

I use SQL for things like this. You can do it in a Data Step, but the SQL is more compact.

data have;
input id  Comp    Num;
datalines;
1   1       2
2   0       3
3   1       1
2   1       3
1   1       2
2   1       3
;
run;

proc sql noprint;
create table want as
select id, 
    sum(comp) as tot, 
    sum(comp)/count(id) as pct
from have
group by id;
quit;

Upvotes: 4

Related Questions