Gropu by sum

[Reddi]

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.

[LinusH]

Use

having sum(amount) = 0

[Ksharp · Accepted solution]

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;

[Reddi]

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

Thanks

[Ksharp]

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;

[Reddi]

Thanks @Ksharp , your brain is really sharp 🙂