Help using Base SAS procedures

need SAS to analyze data within a group

Reply
New Contributor
Posts: 2

need SAS to analyze data within a group

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

UNIQUE_VISITING_IDPATIENT_CLASSCHIEF_COMPLAINTDIAGNOSIS_TEXT
AENULLNULL
AECPNULL
AECPNULL
AICPHEART ATTACK
AICPHEART ATTACK
BEshortbreathNULL
BEshortbreathNULL
BEshortbreathNULL
BESHORTBREATHNULL
CICHECK UPDEFICIENT FE
CICHECK UP DEFICIENT FE
DUNULLNULL
EENULLNULL
EECPNULL
EOCPPOOR SURGERY
EOCPPOOR SURGERY
FENULLNULL
FENULLNULL

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 Smiley Sad  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;

Respected Advisor
Posts: 3,156

Re: need SAS to analyze data within a group

Posted in reply to Farah_Naz

What is your desired output for your sample data?

New Contributor
Posts: 2

Re: need SAS to analyze data within a group

Something like this:

UNIQUE_VISITING_IDFLAG1FLAG2FLAG3FLAG4
A0010
B0010
C0001
D1001
E0010
F1010
Respected Advisor
Posts: 3,156

Re: need SAS to analyze data within a group

Posted in reply to Farah_Naz

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

Respected Advisor
Posts: 3,156

Re: need SAS to analyze data within a group

Posted in reply to Farah_Naz

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

Super User
Posts: 10,046

Re: need SAS to analyze data within a group

Posted in reply to Farah_Naz

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

Ask a Question
Discussion stats
  • 5 replies
  • 231 views
  • 0 likes
  • 3 in conversation