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
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
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
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
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
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.