topic Re: Count of Patients with particular set of diagnoses ever in SAS Programming

Count of Patients with particular set of diagnoses ever

DMMD — Tue, 18 Oct 2016 23:36:18 GMT

Hi,

I have a very large data set (>3 million observations) for which would like to count the number of patients (not total observations) have one of four possible diagnosis codes. Patients could have multiple observations denoting multiple visits and have up to ten diagnosis variable fields.

My four codes of interest are: 72632 or 72631 or 71922 or 71923.

I would like to create a command that searches DX01 to DX10 for all observations for each patient for presence of the above codes.

Example:

Patientid dx01 dx02 dx03 dx04 dx05 … dx10 date

1 72632 72631 71922 71923 5/5/2009

1 72632 71922 70000 70001 6/6/2008

2 70001 70000 1/1/2007

2 71900 71901 2/2/2007

3 72632 71900 2/2/2004

3 72631 70000 3/3/2004

3 71922 4/4/2004

From the above example, I would like to produce output like the following

1. By Patient ID

Patient ID Has (72632 OR 72631 OR 71922 OR 71923) [1=Yes, 2=No]

1 1

2 0

3 1

2. By Total Number of Patients
Total Count of Patients with (72632 OR 72631 OR 71922 OR 71923)

Separately, I would also like a count of the number of observations each patient has for the diagnoses (72632 OR 72631 OR 71922 OR 71923) that produces output like:

Patient ID Has (72632 OR 72631 OR 71922 OR 71923) Number of Visits Addressing Diagnosis

1 1 2

2 0 0

3 1 2

Thanks for your help.

Re: Count of Patients with particular set of diagnoses ever

DarthPathos — Wed, 19 Oct 2016 01:42:40 GMT

I am sure there are more efficient ways to handle this, but here's how I would do it using SQL:

proc sql;
/* This will put the data into categories of Yes = 1 and No = 0 */
create table work.DATA2 as 
select patient_id, 
case when (dx1 in (72632, 72631, 71922, 71923) 
              or (dx2 in (72632, 72631, 71922, 71923) 
.
.
.
            or (dx10 in (72632, 72631, 71922, 71923)) 
then 1 
else 0 end as Yes_No
from work.DATA;

/* This will sum the Yes_No column to give you a TOTAL number of instances that met
the Yes criteria above */
select sum(Yes_No) as Total_Count
from work.DATA2; 

/* This will give you the total per patient ID */
select patient_ID, sum(Yes_No) as Pt_Count
from work.DATA2
group by patient_ID
order by patient_ID;
quit;

This may require some fine tuning, and there are plenty of papers and articles (including a few written by me!) on PROC SQL, but this should get you started.

Good luck and happy coding!

Chris

Re: Count of Patients with particular set of diagnoses ever

Shmuel — Wed, 19 Oct 2016 02:38:27 GMT

Alternative code using data step:

%let ds_check = 72632 72631 71922 71923; /* easy to change list if needed */

data want;

set have;

by patient_id;

retain yes_no;

if first.patient_id then yes_no = 0;

array dx dx1-dx10;

do i=1 to 10;

if dx(i) in (&ds_check) then yes_no = 1;

end;

if last.patient_id then output;

keep patient_id yes_no;

label yes_no = " HAS (&ds_check)";

run;

The TOTAL number can be achieved by various ways

(e.g. by PROC MEANS or PROC SQL or PROC PRINT with SUM )

Re: Count of Patients with particular set of diagnoses ever

Kurt_Bremser — Wed, 19 Oct 2016 08:13:27 GMT

data inter (keep=id diag);
set have;
array diags {*} dx1-dx10;
diag = 0;
do i = 1 to dim(diags);
  if diags{i} in (72632,72631,71922 71923) then diag = 1;
end;
run;

proc sort data=inter;
by id;
run;

data result (keep=id hasdiag visitcount) totalcount (keep=total_patients);
set inter end=done;
retain total_patients 0 visitcount hasdiag;
if first.id
then do;
  visitcount = 0;
  hasdiag = 0;
end;
if diag then do;
  visitcount + 1;
  hasdiag = 1;
end;
if last.id then do;
  output result;
  total_patients + hasdiag;
end;
if done then output totalcount;
run;

If your initial dataset is already sorted by id, you can omit the sort step.

Some edits done for typos.

Re: Count of Patients with particular set of diagnoses ever

DMMD — Wed, 19 Oct 2016 13:15:35 GMT

How do I deal with leading zeros in my diagnoses categories. For example, DX01, DX02, DX03...Dx10?

Thank you so much!

Re: Count of Patients with particular set of diagnoses ever

Shmuel — Wed, 19 Oct 2016 13:23:25 GMT

you may change the array definition into:

ARRAY DX DX01-DX10;

or alternatively do:

SET WANT (Rename=(dx01=dx1 dx02=dx2 ... dx09=dx9));

Re: Count of Patients with particular set of diagnoses ever

DarthPathos — Wed, 19 Oct 2016 13:22:19 GMT

Unless I'm misunderstanding, the DX01 is a column header and at least for the SQL version, won't have an impact. The only time you'd need to worry about leading 0s is if they're in your data (00745454 versus 745454). Sometimes the number of leading 0s is important, and then you would have to treat the column as TEXT rather than NUMERIC.

hope this helps!

Chris

Re: Count of Patients with particular set of diagnoses ever

Kurt_Bremser — Wed, 19 Oct 2016 13:24:00 GMT

@DMMD wrote:

How do I deal with leading zeros in my diagnoses categories. For example, DX01, DX02, DX03...Dx10?

Thank you so much!

Just include the zero in the variable list. Just tested this short example:

data have;
input x01 x02 x03;
cards;
1 2 3
;
run;

data want;
set have;
array xx {*} x01-x03;
do i = 1 to dim(xx);
  put xx{i}=;
end;
run;

Re: Count of Patients with particular set of diagnoses ever

DMMD — Wed, 19 Oct 2016 14:21:51 GMT

Fantastic! The problem was the diagnoses codes were character, not number variables as there are some codes starting with letters. Putting the codes in quotations allowed it to work. Thank you so much! This helped to answer #1 and #3 of my output.

Re: Count of Patients with particular set of diagnoses ever

DMMD — Wed, 19 Oct 2016 21:23:39 GMT

I tried to use your code, but received the following error:

244 data le.leDxTest2 (keep=id hasdiag visitcount diag YEAR eupide DISCHNO ADMDT DISDT ADMWK

244! DISCHWK ADMHR DISHR AGE1 AGE DOB SEX ETHNIC Insurance RACE_M RACE DISP REIMB HOSPCO

244! FACNAME PATST PATCO PATCITY RESIND

245 SDDISCH HSA REG ZIP OPER OPTIME1 OPTIME TOTCHG TOTANCHG TOTCHG1 TOTANCHG1

245! SEQ CNT DX01 DX02 DX03 DX04 DX05 DX06 DX07 DX08 DX09 DX10 HCPCCD1 HCPCCD2 HCPCCD3

246 HCPCCD4 HCPCCD5 HCPCCD6 HCPCCD7 HCPCM1 HCPCM2 HCPCM3 HCPCM4 HCPCM5 HCPCM6

246! HCPCM7 HCPC2M1 HCPC2M2 HCPC2M3 HCPC2M4 HCPC2M5 HCPC2M6 HCPC2M7 DISCHNO1 HCPCCD8

247 HCPCCD9 HCPCCD10 HCPCCD11 HCPCCD12 HCPCCD13 HCPCCD14 HCPCCD15 HCPCCD16

247! HCPCCD17 HCPCCD18 HCPCM8 HCPCM9 HCPCM10 HCPCM11 HCPCM12 HCPCM13 HCPCM14 HCPCM15 HCPCM16

248 HCPCM17 HCPCM18 HCPC2M8 HCPC2M9 HCPC2M10 HCPC2M11 HCPC2M12 HCPC2M13

248! HCPC2M14 HCPC2M15 HCPC2M16 HCPC2M17 HCPC2M18 Side Discharge Died CPI visit) totalcount

248! (keep=total_patients);

249 set le.leDxTest1 end=done;

250 retain total_patients 0 visitcount hasdiag;

251 if first.eupide

252 then do;

253 visitcount = 0;

254 hasdiag = 0;

255 end;

256 if diag then do;

257 visitcount + 1;

258 hasdiag = 1;

259 end;

260 if last.eupide then do;

261 output result;

------

455

ERROR 455-185: Data set was not specified on the DATA statement.

262 total_patients + hasdiag;

263 end;

264 if done then output totalcount;

265 run;

WARNING: The variable id in the DROP, KEEP, or RENAME list has never been referenced.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set LE.LEDXTEST2 may be incomplete. When this step was stopped there were 0

observations and 111 variables.

WARNING: Data set LE.LEDXTEST2 was not replaced because this step was stopped.

WARNING: The data set WORK.TOTALCOUNT may be incomplete. When this step was stopped there

were 0 observations and 1 variables.

WARNING: Data set WORK.TOTALCOUNT was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.09 seconds

cpu time 0.01 seconds

Do you know why this may be a problem?

Thank you!

Re: Count of Patients with particular set of diagnoses ever

Shmuel — Wed, 19 Oct 2016 21:26:35 GMT

on log line 244 your code is:

data le.leDxTest2 (keep=id ...

The ERROR message is very clear:

261 output result; <<<<< should be output le.leDxTest2 <<<<<<<

------

455

ERROR 455-185: Data set was not specified on the DATA statement

Re: Count of Patients with particular set of diagnoses ever

DMMD — Mon, 24 Oct 2016 14:28:37 GMT

Re: Count of Patients with particular set of diagnoses ever

DarthPathos — Wed, 19 Oct 2016 23:33:49 GMT

Hey! I think the issue is the PROC FREQ; if I remember correctly, PROC FREQ counts instances, or something. At a quick glance, your SQL looks good, and i'm not seeing any issues with your brackets or other logic. I'll wait and see what @Shmuel says, and if I can i will do some testing tomorrow when I'm at work.

Good luck 🙂
Chris

Re: Count of Patients with particular set of diagnoses ever

DMMD — Thu, 20 Oct 2016 15:12:51 GMT

Thanks @DarthPathos, @Shmuel, and @Kurt_Bremser! SAS can be a stressful experience to say the least.

Best,

David

Re: Count of Patients with particular set of diagnoses ever

DarthPathos — Wed, 19 Oct 2016 23:45:54 GMT

Yeah, I've got to admit i'm stumped LOL. Hopefully one of my esteemed co-users can point you in the right direction, and if I think of anything I'll you know. One of the benefits of being on the community - you get a wide variety of views 😄
Don't give up - I've been using SAS for 6 years, and have come a long, long way. You'll get there!
Chris

Re: Count of Patients with particular set of diagnoses ever

DMMD — Mon, 24 Oct 2016 14:30:01 GMT

Re: Count of Patients with particular set of diagnoses ever

Shmuel — Thu, 20 Oct 2016 01:34:22 GMT

after log line 742 you got the notes:

NOTE: Variable first.eupide is uninitialized.

NOTE: Variable last.eupide is uninitialized.

on order to check first/last occurence you need a BY statement.

your code should be:

data le.LeDxKB1 (keep = ....);

set le.leDxKB end= done;

by eupide;

.... your code ...

run;

Re: Count of Patients with particular set of diagnoses ever

DMMD — Thu, 20 Oct 2016 15:12:08 GMT

That fixed the problem. Thank you @Shmuel!

Re: Count of Patients with particular set of diagnoses ever

Kurt_Bremser — Thu, 20 Oct 2016 05:25:21 GMT

And keep in mind that retain has no effect on variables contained in the input dataset(s), as these will always be set in the PDV when an observation is read. So you might want to streamline your code there.

And you have a lot of variables present in all of the long lists. Putting them into a macro variable will also help to make the code more readable.

Never underestimate the benefits of proper code formatting. I've seen lots of semantic errors in code here that would have stood out like a beacon if proper indentation had made the blocks easy to determine visually.

Re: Count of Patients with particular set of diagnoses ever

DMMD — Thu, 20 Oct 2016 14:55:52 GMT

Thank you @Kurt_Bremser! I will try to re-work my code to make it more ideal -- still learning at this point. Do you have any good sources on how to create macros and how to line everything up to see typos easily?