Selecting All when Limiting on Data

[IgawaKei29]

Good Morning All,

  I have been trying to wrap my brain around this but I can't seem to come up with an acceptable solution.  I am thinking I might have to use a nested select, but I wanted to ask my question here first.  I am using PROC SQL to select data below.  What I want to happen is to select ID and SUM(Count), however I want to limit this data on value = 20.  The issue is that I want the entire ID to be calculated when this happens, not just limited to the value = 20 line.  Appreciate any guidance on this one.  

Thank you!

ID	Value	Count
1	20	1000
1	19	200
1	18	100
1	17	9992
2	20	10000
3	12	1000
4	13	100
5	12	200
5	13	200
5	20	10000
5	21	2000
6	19	200
7	22	300
7	11	400
7	10	2000

[PeterClemmensen]

What does your desired result look like?

[IgawaKei29]

@PeterClemmensen  It would look like this:

ID	Count
1	11292
2	10000
5	12400

[PeterClemmensen]

Ok. Is it a requirement to do so in PROC SQL?

[IgawaKei29]

@PeterClemmensen If at all possible yes, I am trying to do a SQL passthru.

[PeterClemmensen · Accepted solution]

In Proc SQL:

data have;
input ID Value Count;
datalines;
1 20 1000
1 19 200
1 18 100
1 17 9992
2 20 10000
3 12 1000
4 13 100
5 12 200
5 13 200
5 20 10000
5 21 2000
6 19 200
7 22 300
7 11 400
7 10 2000
;

proc sql;
   create table want as
   select ID, sum(count) as count
   from (select * from have
         group by ID
         having sum(Value = 20) > 0)
   group by ID;
quit;

[IgawaKei29]

Thank you! I appreciate the help. I don't use having often, so I am glad to see it used here.

[novinosrin]

data have;
input ID Value Count;
datalines;
1 20 1000
1 19 200
1 18 100
1 17 9992
2 20 10000
3 12 1000
4 13 100
5 12 200
5 13 200
5 20 10000
5 21 2000
6 19 200
7 22 300
7 11 400
7 10 2000
;

proc sql;
 create table want as
 select id, sum(count) as count
 from have 
 where id in (select id from have where value=20)
 group by id;
quit;

 Basically if i understand you correctly, you want to sum the count for those ID's that have one or more occurance of value =20.