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:
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;
What is your desired output for your sample data?
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 |
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
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
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 ?
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.