topic Re: Selecting All when Limiting on Data in SAS Programming

Selecting All when Limiting on Data

IgawaKei29 — Tue, 03 Nov 2020 11:02:27 GMT

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

Re: Selecting All when Limiting on Data

PeterClemmensen — Tue, 03 Nov 2020 11:06:51 GMT

What does your desired result look like?

Re: Selecting All when Limiting on Data

IgawaKei29 — Tue, 03 Nov 2020 11:09:28 GMT

@PeterClemmensen It would look like this:

ID	Count
1	11292
2	10000
5	12400

Re: Selecting All when Limiting on Data

PeterClemmensen — Tue, 03 Nov 2020 11:16:54 GMT

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

Re: Selecting All when Limiting on Data

IgawaKei29 — Tue, 03 Nov 2020 11:18:58 GMT

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

Re: Selecting All when Limiting on Data

PeterClemmensen — Tue, 03 Nov 2020 11:20:48 GMT

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;

Re: Selecting All when Limiting on Data

IgawaKei29 — Tue, 03 Nov 2020 11:26:46 GMT

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

Re: Selecting All when Limiting on Data

novinosrin — Tue, 03 Nov 2020 12:51:40 GMT


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.