Re: CHI with >0 values

tmm · Posted 04-10-2012 11:08 AM

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

A.SVRTY_LVL_DESC = B.SVRTY_LVL_DESC AND
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 · Posted 04-10-2012 10:45 PM

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 · Posted 04-11-2012 08:20 AM

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 · Posted 04-11-2012 08:40 AM

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 · Posted 04-11-2012 10:39 AM

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.

CHI with >0 values