count help

[tmm]

I have a table that has the following:

comparison              admission                       count

1111                         noreadmit                          0

1111                         readmit                             0

2222                         noreadmit                        12

2222                         readmit                            1

3333                        noreadmit                         12

3333                        readmit                             4

I want to return all the above data but only if there is a sum between readmit and noreadmit >=10 but I need it to also return the readmit associated. This is the code I used but it does not return correctly.

/*********************************GRAB ONLY THOSE TINS THAT HAD A READMIT**************************/

/*********************************FOR CHI ALONG WITH NATIONAL OVERALL****************************/

PROC SQL;

CREATE TABLE ReAdm.CHI2 AS

SELECT *

FROM ReAdm.CHI

GROUP BY COMPARISON

HAVING SUM(UPCASE(ADMISSION)='NOREADMIT' AND COUNT>=10)=0;

RUN;

[FloydNevseta]

proc sql;

create table want as

select

   a.*

from

   have     a

   join

   ( select comparison

      from have

      group by comparison

      having sum( count ) >= 10 )   b

      on

      a.comparison = b.comparison

where

   a.admission = 'readmit'

;

quit;

[tmm]

And this will keep the readmit and noreadmit part of the admission column?

[tmm]

That does not return the right results. The old code that worked for the readmit side was this:

PROC SQL;

CREATE TABLE ReAdm.CHI2 AS

SELECT *

FROM ReAdm.CHI

GROUP BY COMPARISON

HAVING SUM(UPCASE(ADMISSION)='READMIT' AND COUNT=0)=0;

RUN;

But now the goal is to return the readmit and noreadmits where the sum of the readmit and noreadmits has a count of >=10

[Linlin]

what is your wanted output from your sample data?

[tmm]

Nevermind. I was able to take a previous table I created that summed my noreadmits and readmits and give me a totaladmission column and I just created a SAS query off that which says give me the totaladmissions >=10 but leave rest of the columns for me.

[FloydNevseta]

Your initial post said you wanted only the readmits. if now you want both, then remove the where clause.