DATA Step, Macro, Functions and more

Gropu by sum

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

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,028

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;

View solution in original post


All Replies
Super User
Posts: 5,430

Re: Gropu by sum

Use

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

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: 24

Re: Gropu by sum

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

 

Thanks

Super User
Posts: 10,028

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: 24

Re: Gropu by sum

Thanks @Ksharp , your brain is really sharp Smiley Happy

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 147 views
  • 0 likes
  • 3 in conversation