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 |
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;
What does your desired result look like?
Ok. Is it a requirement to do so in PROC SQL?
@PeterClemmensen If at all possible yes, I am trying to do a SQL passthru.
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.