Update counts according to a label

Question

suppose to have the following:

     data have;
     input ID :$20. Label :$20. Hours :$20. Days :$20.;
     cards;
     0001 w  3144  3
     0001 w  23    54
     0001 p  12    1
     0002 m  456   34
     0002 w  2     1
     0002 s  231   45
     0002 w  98    23
     0003 w  12    6
     0003 w  98    76
      ;

Is there a way to, for each ID, sum the Hours, so get the total and then split it by the days but only when the label is == w? If the label is not w put a missing.

Desired output:

     data have;
     input ID :$20. Label :$20. Hours :$20. Days :$20.;
     cards;
     0001 w  167.3158  3
     0001 w  3011.684  54
     0001 p    .       1
     0002 m    .       34
     0002 w  32.79167  1
     0002 s    .       45
     0002 w  754.2084  23
     0003 w  8.048778  6
     0003 w  101.9512  76
      ;

In other words: for 0001 in the desired output example I added: 3144+23+12 = 3179, the 54+3=57 that are the days where the label is "w" then I divided 3179 by 57 and multiplied the result for 3 and 54 but not for 1 respectively.

Thank you in advance

whymath · Answer

Same idea with @Stu Sztukowski, but use DOW-Loop skill:

data want;
  do until(last.id);
    set have;
    by id notsorted;
    sum_of_hours=sum(sum_of_hours,input(hours,best.));
    sum_of_days_w=sum(sum_of_days_w,(label='w')*input(days,best.));
  end;
  do until(last.id);
    set have;
    by id notsorted;
    if label='w' then hours=cats(sum_of_hours*(input(days,best.)/sum_of_days_w));
    else hours='';
    output;
  end;
run;

Stu Sztukowski · Answer

The calculation you need to do looks like this in code form:

if(id = 'w') then hours = sum_of_hours_w/sum_of_days * days
    else hours = .

All we need to do is get the sum of hours and days where label = 'w', then merge it back with our original table by id. The table to do this calculation would look like this:

id	label	hours	days	sum_of_hours	sum_of_days_w
0001	w	3144	3	3179	57
0001	w	23	54	3179	57

You can accomplish this all in a single SQL step.

proc sql;
    create table want as
        select t1.id
             , t1.label
             , CASE(t1.label)
                   when('w') then t2.sum_hours/t2.sum_days_w * t1.days
                   else .
               END as hours
             , t1.days
        from have as t1

        /* Get the sum of all hours and days where label = 'w' */
        LEFT JOIN
            (select id
                  , sum( (label = 'w')*days ) as sum_days_w
                  , sum(hours) as sum_hours
             from have
             group by id
            ) as t2
        ON t1.id = t2.id
    ;
quit;

Update counts according to a label

2 Answers