CHI with >0 values

[tmm]

I have this code:

PROC SQL;

CREATE TABLE ReAdm.CHI AS

SELECT * FROM

(SELECT DISTINCT WHO AS COMPARISON, WHO, SVRTY_LVL_CD, SVRTY_LVL_DESC, ADMISSION, COUNT FROM ReAdm.TINCHI)

UNION ALL

(SELECT DISTINCT A.WHO, "NATIONAL", B.SVRTY_LVL_CD, B.SVRTY_LVL_DESC, B.ADMISSION, B.COUNT

FROM ReAdm.TINCHI AS A INNER JOIN ReAdm.NTLCHI2 AS B

ON A.SVRTY_LVL_CD = B.SVRTY_LVL_CD AND

1. A.SVRTY_LVL_DESC = B.SVRTY_LVL_DESC AND
2. A.ADMISSION = B.ADMISSION)

ORDER BY COMPARISON, SVRTY_LVL_CD, WHO, ADMISSION;

RUN;

The tables it pulls from specifically the TINCHI table has the following information:

who                         svrty_lvl_cd                        svrty_lvl_desc                            admission                       count

1111                              0                                     n/a                                           admit                             12

1111                              0                                     n/a                                          readmit                             0

2222                              0                                     n/a                                           admit                              17

2222                              0                                     n/a                                           readmit                            0

3333                              1                                   minor                                         admit                              29

3333                              1                                   minor                                          readmit                          12

4444                              0                                     n/a                                           admit                              10

4444                              0                                     n/a                                            readmit                            1

The NTLCHI2 looks like this:

who                       svrty_lvl_cd                          svrty_lvl_desc                              admission                       count

national                       0                                       n/a                                          admit                               39

national                       0                                       n/a                                          readmit                              1

national                       1                                       minor                                       admit                               29

national                       1                                       minor                                       readmit                            12

So the above code combines this information but I do not want to include any that have a 0 readmit that are in the TIN table yet I have to retain both the admit and readmit for those TINS that have a readmit of >0. That way the final table based on example above would look like this:

who                    svrty_lvl_cde                         svrty_lvl_desc                               admission                           count

4444                           0                                        n/a                                         admit                                   10

4444                           0                                        n/a                                        readmit                                   1

national                       0                                        n/a                                          admit                                   39

national                      0                                       n/a                                         readmit                                  1

3333                          1                                      minor                                          admit                                   29

3333                          1                                      minor                                         readmit                                 12

national                      1                                      minor                                          admit                                   29

national                      1                                      minor                                         readmit                                 12

[Ksharp]

Not understand what you mean .

data x;
input who     $                    svrty_lvl_cd                        svrty_lvl_desc    $                        admission      $                 count ;
cards;
1111                              0                                     n/a                                           admit                             12
1111                              0                                     n/a                                          readmit                             0
2222                              0                                     n/a                                           admit                              17
2222                              0                                     n/a                                           readmit                            0
3333                              1                                   minor                                         admit                              29
3333                              1                                   minor                                          readmit                          12
4444                              0                                     n/a                                           admit                              10
4444                              0                                     n/a                                            readmit                            1
;
run;
proc sql;
create table want as
select * from
(
 select 'national' as who,svrty_lvl_cd,svrty_lvl_desc,admission,sum(count) as count
  from x
   where  not (admission eq 'readmit' and count eq 0 )
    group by svrty_lvl_cd,svrty_lvl_desc,admission
 union all
 select *
  from x
   group by who,svrty_lvl_cd,svrty_lvl_desc
    having sum(admission eq 'readmit' and count eq 0)  =0
)
order by svrty_lvl_cd,svrty_lvl_desc,who,admission;

quit;

Ksharp

[Haikuo]

Not sure if this is what you want:

data have1;

input (who svrty_lvl_cd svrty_lvl_desc admission) (:$) count;

cards;

1111 0 n/a admit 12

1111 0 n/a readmit 0

2222 0 n/a admit 17

2222 0 n/a readmit 0

3333 1 minor admit 29

3333 1 minor readmit 12

4444 0 n/a admit 10

4444 0 n/a readmit 1

;

data have2;

input (who svrty_lvl_cd svrty_lvl_desc admission) (:$) count;

cards;

national 0 n/a admit 39

national 0 n/a readmit 1

national 1 minor admit 29

national 1 minor readmit 12

;

proc sql;

create table want as

  select * from

(select * from have1 group by who having sum(admission='readmit' and count=0)=0)

  union all

  select * from have2;

  quit;

  proc print;run;

Haikuo

[Haikuo]

if pursuing Data Step approach, here is one:

data have1;

input (who svrty_lvl_cd svrty_lvl_desc admission) (:$) count;

cards;

1111 0 n/a admit 12

1111 0 n/a readmit 0

2222 0 n/a admit 17

2222 0 n/a readmit 0

3333 1 minor admit 29

3333 1 minor readmit 12

4444 0 n/a admit 10

4444 0 n/a readmit 1

;

data have2;

input (who svrty_lvl_cd svrty_lvl_desc admission) (:$) count;

cards;

national 0 n/a admit 39

national 0 n/a readmit 1

national 1 minor admit 29

national 1 minor readmit 12

;

data want;

  do until (last.who);

  set have1 have2;

by who;

_n_=sum(admission='readmit' and count=0);

end;

  do until (last.who);

  set have1 have2;

by who;

if _n_=0 then output;

end;

run;

  proc print;run;

Haikuo

[tmm]

the code works perfect as it is other than I get back any taxID that has a 0 for a readmit as well. I want those not to be included but cannot do the processes you have above as the output does not give me the admit for those that had a readmit. It returns this:

who                         svrty_lvl_cd                        svrty_lvl_desc                            admission                       count

1111                              0                                     n/a                                           admit                             12

national                          0                                    n/a                                            admit                            39

national                         0                                     n/a                                           readmit                             1

2222                              0                                     n/a                                           admit                              17

national                          0                                    n/a                                           admit                               39

national                          0                                    n/a                                           readmit                             1

3333                              1                                   minor                                         admit                              29

3333                              1                                   minor                                          readmit                          12

4444                              0                                     n/a                                           admit                              10

4444                              0                                     n/a                                            readmit                            1

So all it is doing is taking away any tin that had a 0 for a readmit. I need it to remove the tins admit and readmit if they have a readmit of 0. This is because if I attempt to do a chi on this with the number of rows, which I have like 5k tins that have readmits and 50k tins that do not have readmits, the chi will not continue processing because it encounters too many 0 values in the readmit column. I do not want to chi off my 0 readmits anyway so I want any tin that does not have a readmit completely gone from the final output. I only want admits and readmit counts for the tins that have a readmit of 1 or more and then of course I need the national stuff to appear after each valid tin.