Collapse observations by ID as well as some conditions

[CSU_KL]

Hello all,

In the attached example dataset, I want to get total min by ID. But the difficult points have two: first if the group = 0 (in white), the min will not count to the total. Second, if sub-total min of consecutive observations < 10 (in gray) where group = 1, then min will not count to the total either. So basically, I need to the total min in green by ID.

Thank you very much in advance for your help!

Kaigang

[art297]

Not sure if I correctly understand what you want your output to look like. Sounds like the following might be what you're looking for:

data want (drop=total);
  do until (last.group);
    set have;
    by id group notsorted;
    if group ne 0 then do;
      if first.group then total=min;
      else total+min;
    end;
    else total=0;
  end;
  do until (last.group);
    set have;
    by id group notsorted;
    if group ne 0 and total ge 10 then output;
  end;
run;

Art, CEO, AnalystFinder.com

[CSU_KL]

Your codes work. Thank you for your help!

I would see the total so i removed the (drop=total) and get the output below.


| Obs | ID | Min | group | total |
| 1 | 1 | 4 | 1 | 24 |
| 2 | 1 | 5 | 1 | 24 |
| 3 | 1 | 1 | 1 | 24 |
| 4 | 1 | 2 | 1 | 24 |
| 5 | 1 | 3 | 1 | 24 |
| 6 | 1 | 4 | 1 | 24 |
| 7 | 1 | 2 | 1 | 24 |
| 8 | 1 | 3 | 1 | 24 |
| 9 | 1 | 2 | 1 | 14 |
| 10 | 1 | 3 | 1 | 14 |
| 11 | 1 | 4 | 1 | 14 |
| 12 | 1 | 2 | 1 | 14 |
| 13 | 1 | 3 | 1 | 14 |

[PGStats · Accepted solution]

To implement both conditions you need to calculate subtotals:

data have;
input ID Min group @@; 
datalines;
1 2 1 1 3 1 1 4 1 1 5 0 1 2 0 1 3 0 1 4 1 
1 5 1 1 1 1 1 2 1 1 3 1 1 4 1 1 2 1 1 3 1 
1 1 0 1 1 0 1 1 0 1 2 1 1 3 1 1 4 1 1 2 1 
1 3 1 1 2 0 1 1 1 1 2 1 1 1 1 2 1 1 2 2 1 
2 1 1 2 2 0 2 2 0 2 10 0 2 1 0 2 1 1 2 1 1 
2 1 1 2 1 1 2 2 1 2 3 0 2 4 0 2 2 0 2 3 0 
2 3 0 2 1 1 2 3 1 2 1 1 2 1 1 
;

proc print data=have; by id group notsorted; id id group; run;

data want;
tot = 0;
do until (last.ID);
    stot = 0;
    do until (last.group);
        set have;
        by ID group notsorted;
        stot + min;
        end;
    if group ne 0 and not (group = 1 and stot < 10) then 
        tot + stot;
    end;
output;
keep ID tot;
run;

proc print noobs; run;

[CSU_KL]

Thank you! I like the codes.and get what i was looking for.
tot ID
38 1
0 2