Solved
New Contributor
Posts: 3

# Collapse observations by ID as well as some conditions

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.

Kaigang

Accepted Solutions
Solution
‎08-28-2017 01:51 AM
Posts: 5,390

## Re: Collapse observations by ID as well as some conditions

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;``````
PG

All Replies
PROC Star
Posts: 8,091

## Re: Collapse observations by ID as well as some conditions

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

New Contributor
Posts: 3

## Re: Collapse observations by ID as well as some conditions

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 |

Solution
‎08-28-2017 01:51 AM
Posts: 5,390

## Re: Collapse observations by ID as well as some conditions

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;``````
PG
New Contributor
Posts: 3

## Re: Collapse observations by ID as well as some conditions

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

☑ This topic is solved.