Help with code

[tmm]

PROC SQL;

CREATE TABLE ReAdm.TINCounts AS

(SELECT DISTINCT

TIN,

COUNT(ADMIT) AS ADMIT,

SVRTY_LVL_CD,

SVRTY_LVL_DESC

FROM ReAdm.ReAdm1

GROUP BY TIN);

RUN;

This code works but the ReAdm1 table it is pulling from looks like this:

mbr_id              tin              fst_srvc_dt         admit           readmit                  svrty_lvl_cd                      svrty_lvl_desc

11111             xyz               24aug2010             1                 0                              1                                       minor

11111             xyz               22mar2011             1                0                               2                                      major

11111             xyz               16sep2009              1               0                               3                                    extreme

The output from my code above is:

tin           admit                  svrty_lvl_cd              svrty_lvl_desc

xyz           3                            1                        minor

xyz           3                            2                        major

xyz           3                            3                       extreme

I need the output to show 1 for each of the svrty_lvl_cd and not sum the admits all into one.                           

[Haikuo]

Hi,
I am not really sure about your purpose, but given the data as is, the following code will get the output:

PROC SQL;

CREATE TABLE ReAdm.TINCounts AS

SELECT DISTINCT TIN,

ADMIT,

SVRTY_LVL_CD,

SVRTY_LVL_DESC

FROM ReAdm.ReAdm1

;

quit;

Regards,

Haikuo

[RickM]

It seems like you want to group by admit and svrty_lvl_cd unless I am missing something else.

[tmm]

I figured it out based on some of the responses I received. I should be grouping by tin then svrty_lvl_cd.  the output just doing it by tin was this:

tin         admit             svrty_lvl_cd                 desc

1111        12                    1                           minor

1111        12                    2                           major

1111        12                    3                           extreme

Chging and grouping by tin and svrty_lvl_cd outputs this:

tin           admit                   svrty_lvl_cd            desc

1111          1                             1                    minro

1111          8                              2                   major

1111          3                             3                   extreme

Thanks