topic Help with applying sequence number to patient encounters in SAS Programming

Help with applying sequence number to patient encounters

kparker — Tue, 03 Mar 2015 15:23:32 GMT

I have the following code; however, the sequence numbers are not being assigned correctly. I want to create a new variable, sequence number field, and start at 1 when there is a duplicate and then count these. proc sql; create table work.readmits as select t1.encounter_id, t1.medrec_no, t1.first_nm, t1.last_nm, t1.admit_dt, t1.start_dt, t1.end_dt, t1.gender, t1.race, t1.age from inpatient; quit; proc sort data=work.readmits out=rdm; by medrec_no; run; My attempt to create a new variable by using the format statement to add a sequence number field and start at 1 when there is a duplicate which is not working properly. DATA rdm2; SET rdm; format seqno best15.; BY medrec_no; IF FIRST.medrec_no then seqno=1; else seqno=0; RUN;

Re: Help with applying sequence number to patient encounters

Reeza — Tue, 03 Mar 2015 15:57:25 GMT

Please format your code so it's readable, not in a paragraph format.

Re: Help with applying sequence number to patient encounters

kparker — Tue, 03 Mar 2015 20:49:02 GMT

proc sql;

create table work.readmissions as

select

t1.encounter_id,

t1.medrec_no,

t1.first_nm,

t1.last_nm,

t1.admit_dt,

t1.start_dt,

t1.end_dt,

t1.gender,

t1.race,

t1.age

from admclid.inpatient_2012 t1;

quit;

proc sort data=work.readmissions out=rdm;

by medrec_no;

run;

My attempt to create a new variable by using the format statement to add a sequence number field and start at 1 when there is a duplicate which is not working properly.

DATA rdm;

SET rdm;

Format seqno best15.;

BY medrec_no;

IF FIRST.medrec_no then seqno=1; else seqno=0;

RUN;

Re: Help with applying sequence number to patient encounters

Astounding — Tue, 03 Mar 2015 21:35:36 GMT

What does "a duplicate" mean? Would two records for the same MEDREC_NO be a duplicate even if the names and dates are different?

What should SEQNO be if there is only one record for a particular MEDREC_NO?

If there are 5 records for a particular MEDREC_NO, should SEQNO be 1 for the first of the five?

It is possible that your last step is all that needs to be changed:

data rdm2;

set rdm;

by medrec_no;

if first.medrec_no then seqno=1;

else seqno+1;

run;

But the program may change depending on how you answer the questions.

Good luck.

Re: Help with applying sequence number to patient encounters

kparker — Wed, 04 Mar 2015 14:08:30 GMT

Good Morning, Thanks for your response and to answer your questions:

1. An example of what I mean by duplicate is, the patient is the same but has 5 different encounters on different dates. I want the first encounter with a sequence # of 1 and last with a sequence # of 5.

Encounter_No Medrec_No Start_dt End_dt Seq_no

111 123 23DEC2014 28DEC2014 1

122 123 29FEB2014 02MAR2014 2

133 123 23MAR2014 30MAR2014 3

144 123 20SEP2014 25SEP2014 4

155 123 07APR2014 12APR2014 5

2. Using the example above, two records for the same Medrec_no will not be duplicate because the encounter_no is different. If the encounter_no was the same then they would be considered a duplicate.

3. I am really only interested in the records that have patients with multiple encounters. If the patient has only one encounter they would not be considered a readmission.

Re: Help with applying sequence number to patient encounters

MarkWik — Wed, 04 Mar 2015 15:21:17 GMT

A sample Input dataset and wanted output dataset please? Would make it easy for people to help you

Re: Help with applying sequence number to patient encounters

Astounding — Wed, 04 Mar 2015 15:21:45 GMT

Based on your answers, here's how I would program it. Once you have extracted the data:

proc sort data=rdm;

by medrec_no encounter_no;

run;

data rdm2;

set rdm;

by medrec_no encounter_no;

if first.encounter_no=0 or last.encounter_no=0 then duplicate_encounter_data_flag='Y';

if first.medrec_no then do;

if last.medrec_no=0 then seqno=1;

end;

else if first.encounter_no then seqno + 1;

run;

See how that matches what you are hoping to do.

Good luck.

Re: Help with applying sequence number to patient encounters

kparker — Wed, 04 Mar 2015 19:14:29 GMT

The first part of the code appears to work when checking for duplicates by encounter number and flagging with a Y. The dataset did not have any duplicate encounters so this column was blank for each observation.

The second part of the code using the medrec_no to set the seqno to 1 did not work. All observations have a 0 for seqno even the patient with multiple encounters.

Re: Help with applying sequence number to patient encounters

kparker — Wed, 04 Mar 2015 19:22:07 GMT

Encounter_No Medrec_No Start_dt End_dt Seq_no

111 123 23DEC2014 28DEC2014 1

122 123 29FEB2014 02MAR2014 2

133 123 23MAR2014 30MAR2014 3

144 123 20SEP2014 25SEP2014 4

155 123 07APR2014 12APR2014 5

2. Using the example above, two records for the same Medrec_no will not be duplicate because the encounter_no is different. If the encounter_no was the same then they would be considered a duplicate.

3. I am really only interested in the records that have patients with multiple encounters. If the patient has only one encounter they would not be considered a readmission.

Re: Help with applying sequence number to patient encounters

Astounding — Wed, 04 Mar 2015 19:24:16 GMT

That should be easy to diagnose and fix. The necessary information that you have to post is the SAS log from that final DATA step, including any notes or warnings.

Re: Help with applying sequence number to patient encounters

kparker — Wed, 04 Mar 2015 19:39:45 GMT

I did not receive any errors or warnings, however, the SAS log is below:

The SAS System 07:45 Wednesday, March 4, 2015

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROJECTPATH='\\DHH-ISB-FS01\User Folders\kparker\My Documents\SAS Data
4        ! Sets\Readmissions.egp';
5          %LET _CLIENTPROJECTNAME='Readmissions.egp';
6          %LET _SASPROGRAMFILE=;
7
8          ODS _ALL_ CLOSE;
9          OPTIONS DEV=ACTIVEX;
10         GOPTIONS XPIXELS=0 YPIXELS=0;
11         ODS LISTING GPATH=&sasworklocation;
12         FILENAME EGHTML TEMP;
13         ODS HTML(ID=EGHTML) FILE=EGHTML ENCODING='utf-8' STYLE=HtmlBlue
13       ! STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/5.1/Styles/HtmlBlue.
13       ! css")
13       ! ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v93/sasgraph.exe#version=9,3")
13       ! NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation;
NOTE: Writing HTML(EGHTML) Body file: EGHTML
14         FILENAME EGRTF TEMP;
15         ODS RTF(ID=EGRTF) FILE=EGRTF ENCODING='utf-8' STYLE=Rtf NOGTITLE NOGFOOTNOTE;
NOTE: Writing RTF Body file: EGRTF
16
17         GOPTIONS ACCESSIBLE;
18         DATA rdm2; *not working;
19         SET work.rdm;
20         by encounter_id medrec_no;
21         if first.encounter_id=0 or last.encounter_id=0
22         then duplicate_encounter_data_flag="Y";
23         if first.medrec_no then do;
24         if last.medrec_no=0 then seqno=1;
25         end;
26         else if first.encounter_id then seqno + 1;
27         run;

NOTE: There were 557758 observations read from the data set WORK.RDM.
NOTE: The data set WORK.RDM2 has 557758 observations and 13 variables.
NOTE: DATA statement used (Total process time):
      real time           1.15 seconds
      cpu time            1.15 seconds

28
29         GOPTIONS NOACCESSIBLE;
30         %LET _CLIENTTASKLABEL=;
31         %LET _CLIENTPROJECTPATH=;
32         %LET _CLIENTPROJECTNAME=;
33         %LET _SASPROGRAMFILE=;
34
35         ;*';*";*/;quit;run;
36         ODS _ALL_ CLOSE;
37
38
39         QUIT; RUN;
2                                           The SAS System              07:45 Wednesday, March 4, 2015

Re: Help with applying sequence number to patient encounters

Astounding — Wed, 04 Mar 2015 19:45:26 GMT

It looks like your BY statement needs to be fixed. It reads:

by encounter_id medrec_no;

It should read:

by medrec_no encounter_id;

That change should be made in two places, both PROC SORT and the DATA step. Let's see if that clears it all up.

Re: Help with applying sequence number to patient encounters

kparker — Wed, 04 Mar 2015 19:55:22 GMT

That fixed it! But how can I make the other observations that are not in a sequence set to 0 or some other flag? They are being populated with various numbers (i.e. 2, 4, 5, etc.)

Re: Help with applying sequence number to patient encounters

naveen_srini — Wed, 04 Mar 2015 20:00:44 GMT

data testdata;

input e_no m_no st_dt date9. end_dt :date9.;

format st_dt end_dt date9.;

output;

if _n_=1 then output;

datalines;

111 123 23DEC2014 28DEC2014

122 123 28FEB2014 02MAR2014

133 123 23MAR2014 30MAR2014

144 123 20SEP2014 25SEP2014

;

data want_data;

set testdata;

by e_no m_no notsorted;

if e_no ne lag(e_no) then seq+1;

if first.e_no and last.e_no then considered="Noadmission";

else considered="readmission";

run;

Re: Help with applying sequence number to patient encounters

Astounding — Wed, 04 Mar 2015 20:01:56 GMT

Sorry, I should have noticed that. Take this line:

if last.medrec_no=0 then seqno=1;

Add another line just after that:

else seqno=0;

That should assign 0 to all those that are unique.

Re: Help with applying sequence number to patient encounters

kparker — Wed, 04 Mar 2015 20:30:47 GMT

Yes!!! That did it. Thank you so much for your help!!