need SAS to analyze data within a group

[Farah_Naz]

I'm trying to analyze some grouped data in SAS 9.3. This is how its structured (not the actual data):

UNIQUE_VISITING_ID	PATIENT_CLASS	CHIEF_COMPLAINT	DIAGNOSIS_TEXT
A	E	NULL	NULL
A	E	CP	NULL
A	E	CP	NULL
A	I	CP	HEART ATTACK
A	I	CP	HEART ATTACK
B	E	shortbreath	NULL
B	E	shortbreath	NULL
B	E	shortbreath	NULL
B	E	SHORTBREATH	NULL
C	I	CHECK UP	DEFICIENT FE
C	I	CHECK UP	DEFICIENT FE
D	U	NULL	NULL
E	E	NULL	NULL
E	E	CP	NULL
E	O	CP	POOR SURGERY
E	O	CP	POOR SURGERY
F	E	NULL	NULL
F	E	NULL	NULL

With each unique UNIQUE_VISITING_ID being a single patient visit (so 6 visits total in this set)

(please note that the real data is not ordered or sorted in any way)

I need to count the number of visits where:

• DIAGNOSIS_TEXTand CHIEF_COMPLAINT are always null (never have a real value in there) - 2
• CHIEF_COMPLAINT is null, but DIAGNOSIS_TEXT is NOT null - 0
• PATIENT_CLASS is 'E' at least once within the visit - 4
• PATIENT_CLASS class is NEVER 'E' within the visit - 2

Plus, how to remove a group where PATIENT_CLASS is never E

Any ideas? I've tried it a few different ways and I worry it's not working like I want it to. Specifically, I'd imagine the two patient class ones should add up to the total number of visits, but they're actually much more than the total   Would really appreciate any help!!

----

I've tried the following:

PROC SQL;

CREATE TABLE ERRORS_&PLACE

AS SELECT UNIQUE_VISITING_ID,

(case when (max(DIAGNOSIS_TEXT)is null and max(CHIEF_COMPLAINT)is null) then 1 else 0 end) as FLAG1,

(case when(max(DIAGNOSIS_TEXT)is not null and max(CHIEF_COMPLAINT)is null) then 1 else 0 end) as FLAG2,

(case when (PATIENT_CLASS) = 'E' then 1 else 0 end) as flag3,

(case when(PATIENT_CLASS) = 'E' then 0 else 1 end) as flag4

from &Place

  group by UNIQUE_VISITING_ID;

run;

quit;

PROC SQL;

create table Errors2_&Place

as select distinct(UNIQUE_VISITING_ID),

SUM(FLAG1) AS FLAG1,

SUM(FLAG2) AS FLAG2,

SUM(FLAG3) AS FLAG3,

SUM(FLAG4) AS FLAG4

FROM ERRORS_&PLACE

;

RUN;

QUIT;

---------------

Have also tried:

PROC SQL;

CREATE TABLE ERRORS_&PLACE

AS SELECT UNIQUE_VISITING_ID,

(case when ((DIAGNOSIS_TEXT) is null and (CHIEF_COMPLAINT) is null and max(DIAGNOSIS_CODE) is null) then 1 else 0 end) as FLAG1,

(case when(max(DIAGNOSIS_TEXT) is not null) and (max(CHIEF_COMPLAINT) is null) then 1 else 0 end) as FLAG2,

MAX(case when (PATIENT_CLASS) = 'E' then 1 else 0 end) as flag3,

MAX(case when(PATIENT_CLASS) = 'E' then 0 else 1 end) as flag4

from &Place

  group by UNIQUE_VISITING_ID;

run;

quit;

PROC SQL;

create table Errors2_&Place

as select distinct(UNIQUE_VISITING_ID),

MAX(FLAG1) AS FLAG1,

MAX(FLAG2) AS FLAG2,

MAX(FLAG3) AS FLAG3,

MAX(FLAG4) AS FLAG4

FROM ERRORS_&PLACE

group by UNIQUE_VISITING_ID

PROC SQL;

create table Errors3_&Place

as select distinct(UNIQUE_VISITING_ID),

SUM(FLAG1) AS FLAG1,

SUM(FLAG2) AS FLAG2,

SUM(FLAG3) AS FLAG3,

SUM(FLAG4) AS FLAG4

FROM ERRORS_&PLACE

;

RUN;

QUIT;

[Haikuo]

What is your desired output for your sample data?

[Farah_Naz]

Something like this:

UNIQUE_VISITING_ID	FLAG1	FLAG2	FLAG3	FLAG4
A	0	0	1	0
B	0	0	1	0
C	0	0	0	1
D	1	0	0	1
E	0	0	1	0
F	1	0	1	0

[Haikuo]

In this case, it is even simpler:

PROC SQL;

CREATE TABLE WANT AS

SELECT UNIQUE_VISITING_ID, NOT(SUM(DIAGNOSIS_TEXT NE 'NULL' OR CHIEF_COMPLAINT NE 'NULL')) AS FLAG1, /* COUNT =0*/

          NOT(SUM(CHIEF_COMPLAINT NE 'NULL' OR DIAGNOSIS_TEXT EQ 'NULL')) AS FLAG2,/* COUNT =0*/

             SUM(PATIENT_CLASS EQ 'E')>0 AS FLAG3 , /*COUNT >0*/

          SUM(PATIENT_CLASS EQ 'E')=0 AS FLAG4 /* COUNT =0*/

             FROM HAVE GROUP BY UNIQUE_VISITING_ID    

;

QUIT;

Haikuo

[Haikuo]

data have;

infile cards dlm='09'x;

input (UNIQUE_VISITING_ID  PATIENT_CLASS   CHIEF_COMPLAINT) (:$20.)     DIAGNOSIS_TEXT :$&20.;

cards;

A    E    NULL NULL

A    E    CP   NULL

A    E    CP   NULL

A    I    CP   HEART ATTACK

A    I    CP   HEART ATTACK

B    E    shortbreath     NULL

B    E    shortbreath     NULL

B    E    shortbreath     NULL

B    E    SHORTBREATH     NULL

C    I    CHECK UP   DEFICIENT FE

C    I    CHECK UP   DEFICIENT FE

D    U    NULL NULL

E    E    NULL NULL

E    E    CP   NULL

E    O    CP   POOR SURGERY

E    O    CP   POOR SURGERY

F    E    NULL NULL

F    E    NULL NULL

;

/*THIS GIVES YOU THE COUNTS*/

PROC SQL;

CREATE TABLE COUNT AS

SELECT SUM(_FIRST=0) AS FIRST,

           SUM(_SECOND=0) AS SECOND,

              SUM(_THIRD>0) AS THIRD,

              SUM(_THIRD=0) AS FORTH

FROM

(SELECT SUM(DIAGNOSIS_TEXT NE 'NULL' OR CHIEF_COMPLAINT NE 'NULL') AS _FIRST, /* COUNT =0*/

          SUM(CHIEF_COMPLAINT NE 'NULL' OR DIAGNOSIS_TEXT EQ 'NULL') AS _SECOND,/* COUNT =0*/

             SUM(PATIENT_CLASS EQ 'E') AS _THIRD, /*COUNT >0*/

          SUM(PATIENT_CLASS EQ 'E') AS _FORTH /* COUNT =0*/

             FROM HAVE GROUP BY UNIQUE_VISITING_ID )  

;

QUIT;

/*THIS REMOVES GROUP NEVER E*/

PROC SQL;

CREATE TABLE WANT AS

SELECT * FROM HAVE

GROUP BY  UNIQUE_VISITING_ID

HAVING SUM(PATIENT_CLASS EQ 'E') >0

;

QUIT;

Haikuo

[Ksharp]

Use HaiKuo's data and partial code .

Your second one is ambiguous. you want all of CHIEF_COMPLAINT be NULL and all of DIAGNOSIS_TEXT not be NULL ?

• CHIEF_COMPLAINT is null, but DIAGNOSIS_TEXT is NOT null - 0

data have;
infile cards expandtabs;
input (UNIQUE_VISITING_ID  PATIENT_CLASS   CHIEF_COMPLAINT) (:$20.)     DIAGNOSIS_TEXT & $20.;
cards;
A    E    NULL NULL
A    E    CP   NULL
A    E    CP   NULL
A    I    CP   HEART ATTACK
A    I    CP   HEART ATTACK
B    E    shortbreath     NULL
B    E    shortbreath     NULL
B    E    shortbreath     NULL
B    E    SHORTBREATH     NULL
C    I    CHECK UP   DEFICIENT FE
C    I    CHECK UP   DEFICIENT FE
D    U    NULL NULL
E    E    NULL NULL
E    E    CP   NULL
E    O    CP   POOR SURGERY
E    O    CP   POOR SURGERY
F    E    NULL NULL
F    E    NULL NULL
;
run;

proc sql;
create table x as
 select UNIQUE_VISITING_ID,((sum( DIAGNOSIS_TEXT eq 'NULL') eq count(*)) and
                           (sum(CHIEF_COMPLAINT eq 'NULL') eq count(*)) ) as flag1,
                                 ((sum( DIAGNOSIS_TEXT ne 'NULL') eq count(*)) and
                           (sum(CHIEF_COMPLAINT eq 'NULL') eq count(*)) ) as flag2,
                                 (sum( PATIENT_CLASS eq 'E') ge 1)  as flag3,
                           (sum( PATIENT_CLASS eq 'E') eq 0)  as flag4
from have
 group by UNIQUE_VISITING_ID ;
quit;



/*THIS REMOVES GROUP NEVER E*/
PROC SQL;
CREATE TABLE WANT AS
SELECT * FROM HAVE
GROUP BY  UNIQUE_VISITING_ID
HAVING SUM(PATIENT_CLASS EQ 'E') >0
;
QUIT;

Xia Keshan

Message was edited by: xia keshan