sas code help

[tmm]

/*********************************GET TINS WITH TOTAL ADMISSIONS >=10 ONLY*********************/

PROC SQL;

CREATE TABLE ReAdm.TIN_Merge3 AS

(SELECT *

FROM ReAdm.TIN_Merge2

WHERE ADMIT >=10

GROUP BY TIN);

RUN;

I need to code this to grab all my severities. Right now it will only grab those that have admit >=10 and my example table looks like this

who              svrty              admit

1111              1                    5

1111              2                    4

1111              3                   12

1111              4                    7

If I run the code above it only returns the svrty 3 and I need it actually to return all because the admits summed together are 28. I guess I need to code it where the sum of admit >=10 so it will return all svrty items.

[Tom]

PROC SQL;

CREATE TABLE ReAdm.TIN_Merge3 AS

  SELECT *

  FROM ReAdm.TIN_Merge2

  GROUP BY TIN

  having sum(ADMIT) >=10

;

RUN;

[tmm]

That does not return anything other than

who        svrty             admit

1111         3                  12

It leaves out the other svrty codes which I need them to be returned. A case where it would not be returned is this:

who         svrty          admit

2222          1              1

2222          2              3

2222          3              0

2222          4               0

None of these would be returned because the sum of the admit is not >=10 it is 4

I need all severities returned if the who has an admit sum of >=10 so in my first example all 4 severities should be returned because the sum of the TIN is 28

[Tom]

From your example data your group by variable should be WHO not TIN.

[Linlin · Accepted solution]

try:

PROC SQL;

CREATE TABLE ReAdm.TIN_Merge3 AS

  SELECT *

  FROM ReAdm.TIN_Merge2

  where tin in (select tin from ReAdm.TIN_Merge2

  GROUP BY TIN

  having sum(ADMIT) >=10)

;

quit;