topic Re: Collapse observations by ID as well as some conditions in SAS Data Management

Collapse observations by ID as well as some conditions

CSU_KL — Sun, 27 Aug 2017 21:53:00 GMT

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

Re: Collapse observations by ID as well as some conditions

art297 — Sun, 27 Aug 2017 22:35:28 GMT

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

Re: Collapse observations by ID as well as some conditions

PGStats — Mon, 28 Aug 2017 04:25:40 GMT

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;

Re: Collapse observations by ID as well as some conditions

CSU_KL — Mon, 28 Aug 2017 05:51:21 GMT

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 |

Re: Collapse observations by ID as well as some conditions

CSU_KL — Mon, 28 Aug 2017 05:53:06 GMT

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