topic Re: Gropu by sum in SAS Programming

Gropu by sum

Reddi — Mon, 20 Mar 2017 11:02:50 GMT

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

2 -100

2 0

2 100

2 0

3 0

3 100

3 200

;

run;

Expected Output is ID : 1

Any help is appreciated.

Re: Gropu by sum

LinusH — Mon, 20 Mar 2017 11:18:47 GMT

Use

having sum(amount) = 0

Re: Gropu by sum

Ksharp — Mon, 20 Mar 2017 11:34:59 GMT


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;

Re: Gropu by sum

Reddi — Mon, 20 Mar 2017 11:48:14 GMT

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

Thanks

Re: Gropu by sum

Ksharp — Mon, 20 Mar 2017 11:57:23 GMT


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;

Re: Gropu by sum

Reddi — Mon, 20 Mar 2017 12:36:09 GMT

Thanks @Ksharp , your brain is really sharp 🙂