Solved
Contributor
Posts: 29

# Gropu by sum

[ Edited ]

Hi , I've the below dataset and need the ID who has the amount as 0 throuhout the lifecycle of the ID.

data have;

input ID Amount;

cards;

1 0

1 0

1 0

1 0

2 -100

2 0

2 100

2 0

3 0

3 100

3 200

;

run;

Expected Output is ID : 1

Any  help is appreciated.

Accepted Solutions
Solution
‎03-20-2017 08:36 AM
Super User
Posts: 10,784

## Re: Gropu by sum

``````
data have;
input ID Amount;
cards;
1 0
1 0
1 0
1 0
2 -100
2 0
2 100
2 0
3 0
3 100
3 200
;
run;
proc sql;
select distinct id
from have
group by id
having min(amount)=0 and max(amount)=0;
quit;``````

All Replies
Super User
Posts: 5,882

## Re: Gropu by sum

Use

``having sum(amount) = 0``
Data never sleeps
Solution
‎03-20-2017 08:36 AM
Super User
Posts: 10,784

## Re: Gropu by sum

``````
data have;
input ID Amount;
cards;
1 0
1 0
1 0
1 0
2 -100
2 0
2 100
2 0
3 0
3 100
3 200
;
run;
proc sql;
select distinct id
from have
group by id
having min(amount)=0 and max(amount)=0;
quit;``````
Contributor
Posts: 29

## Re: Gropu by sum

Actually i need to add another indicator variable showing Y if the condition mets.

Thanks

Super User
Posts: 10,784

## Re: Gropu by sum

``````
data have;
input ID Amount;
cards;
1 0
1 0
1 0
1 0
2 -100
2 0
2 100
2 0
3 0
3 100
3 200
;
run;
proc sql;
select *,case when min(amount)=0 and max(amount)=0 then 'Y' else 'N' end as flag
from have
group by id;
quit;``````
Contributor
Posts: 29

## Re: Gropu by sum

Thanks @Ksharp , your brain is really sharp

☑ This topic is solved.