Adding a flag column for data with multiple rows per patient

[banaz]

Hi, 

I have a vertical data (multiple rows per patient) as follows: 

ID	Service
111	Comprehensive Assessment
111	Psychology Intake
111	Psychology Session
111	Psychology Discharge
123	Psychiatry Assessment
123	Psychiatry Intake
123	Psychiatry Session
123	Psychiatry Discharge
124	Comprehensive Assessment
124	Outpatient MH Intake
124	Outpatient MH Day

Each patient can get multiple services as shown in the table above. I would like to add a new flag column to calculate the % of patients who converted to treatment. For example, I would like to know who converted from 'Comprehensive Assessment" to 'Psychology Intake" or conversion from 'Psychiatry Assessment' to 'Psychiatry Intake', etc.

Ideally, I would like the table above to look like something below with a new treatment flag column;

ID	Service	Treatment_flag
111	Comprehensive Assessment	1
111	Psychology Intake	1
111	Psychology Session	0
111	Psychology Discharge	0
123	Psychiatry Assessment	2
123	Psychiatry Intake	2
123	Psychiatry Session	0
123	Psychiatry Discharge	0
124	Comprehensive Assessment	3
124	Outpatient MH Intake	3
124	Outpatient MH Day	0

I have no idea how to do this in SAS. Any help would be greatly appreciated. 

Thanks, 

-Banaz

[novinosrin]

The pattern seems to be 

1. Assessment

2. Intake

the above 2 words in sequence????

[banaz]

@novinosrin Yes that is correct. The 'Intake' denotes that the patient is going through a treatment. So I would like to flag from 'Assessment' (there are different kinds of assessments a patient can get but keeping it simple here) to treatment 'Intake'. I would like to know the % of patients going from assessment to treatment intake.

[mkeintz]

And precisely what does "convert to treatment" mean?    How do you determine from which status the convert to treatment occurred?  Do you convert to treatment only from the immediately preceding status?  (single count per patient).  Or can you convert to treatment from any of several preceding statuses?

[banaz]

@mkeintz the code under the 'Service' column is specific to the type of service they receive. The data i'm using has tracked all the services that a patient has received (until they are discharged). Majority of our patients have assessments first then they either convert to treatment or dont. And each service has a date attached to it (not sure if I need to use the date column).

To answer your question, we want to know how many patients started off with an assessment fist, then were referred to treatment services. Majority will start with an assessment first, and then get called in for an 'intake' which we call (first day of treatment).

After a patient starts treatment, they could get a follow up service. I would like to flag assessment and intake services, then be able to flag those two to be able to know whether they converted to treatment or not. Hope this clarifies things.

[novinosrin]

HI @banaz  Assuming I understand the requirement

data have;
input ID	Service & $30.;
cards;
111	Comprehensive Assessment
111	Psychology Intake
111	Psychology Session
111	Psychology Discharge
123	Psychiatry Assessment
123	Psychiatry Intake 
123	Psychiatry Session
123	Psychiatry Discharge
124	Comprehensive Assessment
124	Outpatient MH Intake
124	Outpatient MH Day
;

data want;
 do _n_=1 by 1 until(last.id);
  set have;
  by id;
  if upcase(scan(service,-1,' ')) in ('ASSESSMENT','INTAKE') then _iorc_+1;
 end;
 do _n_=1 to _n_;
  set have;
  Treatment_Flag=0;
  if _iorc_=2 then do;
  if _n_=1 then t+1;
  if upcase(scan(service,-1,' ')) in ('ASSESSMENT','INTAKE') then Treatment_Flag=t;
  end;
  output;
 end;
 _iorc_=.;
 drop t;
run;

[banaz]

@novinosrin thanks ! It seems to be working with the example dataset I provided. Can you please clarify the following to make sure I understand what the code is doing so I can tweak to make it work on my own dataset: 

if upcase(scan(service,-1,' ')) in ('ASSESSMENT','INTAKE') then _iorc_+1;

instead of writing 'ASSESSMENT, 'INTAKE' can I specifically call out the exact service name ? For example, 'Comprehensive Assessment', Psychology Intake' and so on. I've provided a very simplistic view of our data. Some assessment services are called for example 'Comp Ax - Rpt w/in 15bd' or 'Comp Ax - Rpt over 15bd', corresponding to comprehensive assessment within 15 bd or comprehensive assessment report over 15 bd, both are assessment services

Also, what does _iorc+1 stand for?  

[novinosrin]

Hi @banaz   I am basically checking whether 
'ASSESSMENT','INTAKE' exist in sequence for each ID by group. So if both exist in sequence the count should be 2. Once this is determined, increment the FLAG variable. 

_iorc_ and _n_ are all temp variables. Nothing more, nothing less. Just convenience to avoid writing them in drop statements!

[banaz]

Hi @novinosrin , so if the assessment and intake services are not in sequence, then it will miss it?  And by sequence, do you mean that the intake service has to be right below the assessment service?

Is there any other way to do this without sequence? And can I reference the exact service code instead of just referencing any assessments and any intakes. 

Thanks so much again.

[novinosrin]

Hi @banaz  That code will handle whether or not ASSESSMENT and INTAKE occur in sequence(one after another record). So no problem with that. Basically it only checks or finds whether both ASSESSMENT and INTAKE exists for an ID by group. If it finds one, _iorc_ counter variable gets incremented by one. If it finds both, _iorc_ gets a value of 2. For our FLAG variable, we need the _iorc_ variable to have a value of 2. 

Okay, Now what if

1. ASSESSMENT or INTAKE occurs more than once without the other. Then yes, the code will cause incorrect results because the increment would have happened. But this is where I wouldn't know what's in your data or in others can such a thing happen?

2. The code goes by the logic, read a record, check for those 2 keywords, if found increment the counter. On the 2nd pass, if the counter for a particular ID group has a value of 2, the Flag variable for those records with the keywords gets a value of 1. 

3. I have assumed either one or both will exist, but one of the two will not exist more than once for an ID by group i.e

ID =1  Assessment _iorc_=1

ID=1 Assessemt   _iorc_=2   

No Intake here, so this is what I mean in point 1.

---

@banaz wrote:

Hi @novinosrin , so if the assessment and intake services are not in sequence, then it will miss it?  And by sequence, do you mean that the intake service has to be right below the assessment service?

Is there any other way to do this without sequence? And can I reference the exact service code instead of just referencing any assessments and any intakes. 

Thanks so much again.

---

[banaz]

Hi @novinosrin Thanks for the clarification, I see what you're doing here. 

Yes, there could be more than one ASSESSMENT (but of different kind, not the same type) but because the code is only scanning for one word 'ASSESSMENT', it will give false results. I tried your code to match it to the exact service name but it didn't work. Is there anyway to scan for the entire service name and then assign a value to it? See table below for a sample of my data: 

ID	Service
111	EFTx - Block 1 - Session
111	EFTx - Block 1 - Session
111	EFTx - Block 1 - Session
111	EFTx - Block 1 - Session
111	EFTx - Block 1 - Session
111	EFTx - Block 1 - Session
111	EFTx - Block 1 - Session
111	Follow Up - Rpt over 5bd - Rehab
111	Follow Up - Rpt w/in 5 bd - Rehab
111	MSK Comprehensive Assessment - Pain
111	Pharmacy Screen
111	Psychology Assessment - MSK
111	EFTx - Block 1 - Intake
111	EFTx - Block 1 - Progress
111	EFTx - Block 1 - Session
111	EFTx - Block 1 - Session
111	EFTx - Block 1 - Session
111	EFTx - Block 1 - Discharge (8 visits and over)
222	Psychiatry Session
222	Psychiatry Progress
222	Psychiatry Session
222	Psychiatry Session
222	Psychiatry Session
222	Psychiatry Session
222	Psychiatry Session
222	Psychiatry Assessment
333	Comp Ax - Rpt over 15bd
333	Psychology Intake
333	Psychology Progress
333	Psychology Session
333	Psychology Session
333	Psychology Session
333	Psychology Session
333	Psychology Session
333	Psychology Session
333	Psychosocial Intake
333	Psychosocial Progress
333	Psychosocial Session
333	Psychosocial Session
333	Psychosocial Session
333	Psychosocial Session
333	Psychosocial Session
333	Psychosocial Session

There are different kinds of assessments & treatment services a patient can get. For example, I'm interested in flagging only mental health services. ID 111 has 'Psychology Assessment - MSK' but no intake, so they did not convert to mental health treatment in this case.

ID 222 has 'Psychiatry Assessment' and  'Psychiatry Session' to denote treatment. ID 333 has  'Comp Ax - Rpt over 15bd' for their assessment and 'Psychology Intake' for their treatment. As you can see, there are different service names for different kinds of assessments and treatments. 

How would I tweak your code to be able to scan for the exact word match? 

Thanks!

-Banaz

[novinosrin]

Thank you @banaz  Things are much clearer now. 

 Yes we can match a full service assessment and full service intake as a whole, meaning a complete match. However how would we map cases like  'Comp Ax - Rpt over 15bd' for their assessment and 'Psychology Intake' for their treatment. The naming convention has to have some pattern for the LOOK UP to "find an fetch".

For example,  'Psychology Assessment' and 'Psychology Intake' is kinda neat pair whereas though close Psychiatry Assessment and Psycology Intake precludes a direct look up. Does this make sense?

[banaz]

@novinosrin I see, I'm not familiar with the look up function. Maybe we could rename service names with 'Comp Ax - Rpt over 15bd' to something else that will give a pattern? And for Psychiatry Assessment they will get a Psychiatry Intake (they won't get Psychology Intake).

To make it more clear, I would like to flag the following possible streams, to see how many converted from different assessment types to their respective treatment intakes (the names below are service names we use at our clinic)

1) Comprehensive Assessment to Psychology Intake : 

Service names for the assessment is 'Comp Ax - Rpt w/in 15bd' or 'Comp Ax - Rpt over 15bd'

Treatment name is 'Psychology Intake'

2) Comprehensive Assessment to Psychiatry Intake : 

Service names for the assessment is 'Comp Ax - Rpt w/in 15bd' or 'Comp Ax - Rpt over 15bd'

Treatment name is 'Psychiatry Intake'

3) Comprehensive Assessment to Psychosocial Intake : 

Service names for the assessment is 'Comp Ax - Rpt w/in 15bd' or 'Comp Ax - Rpt over 15bd'

Treatment name is 'Psychosocial Intake'

4) Comprehensive Assessment to Outpatient MH:

Service names for the assessment is 'Comp Ax - Rpt w/in 15bd' or 'Comp Ax - Rpt over 15bd'

Treatment name is 'Outpatient MH Intake'

5) Single assessment to single treatment intake: 

'Psychology Assessment' to 'Psychology Intake'

'Psychiatry Assessment' to 'Psychiatry Intake'

'Psychosocial Assessment' to 'Psychosocial Intake'

Is this possible to do with the LOOKUP function or any other functions? 

Hope I didn't complicate things.

[novinosrin]

Hi @banaz   Now we are talking 🙂 Okay, so it seems you can define what INTAKE belongs to what SERVICES category. Okay, so syntactically we can define that using formats up front and then use those definitions as format look ups in the logic. 

There is a catch though, that you need to clarify

 'Comp Ax - Rpt w/in 15bd' or 'Comp Ax - Rpt over 15bd' as assessment is associated with all 4 different intakes  from 'Psychology--Outpatient MH ' 

Let's take a step back.

My attempt to understand:

A patient presumably comes in with a condition for an assessment. This assessment could be comprehensive. Once the assessment is done, the specifics are determined. The specific is denoted as Intake accordingly.  The specific intake is present will have to be incremented across patients. Also, an assessment will not necessarily lead to an INTAKE. However, could there be a possibility of a patient's assessment leading to more than one intake?

Let me also request @Reeza  and @PaigeMiller their time if they don't mind and have the interest to see if they comprehend your need. The coding is not a challenge in my opinion but just understanding the requirement properly is indeed the hard nut to crack.

[banaz]

@novinosrin great, yes- you understood it correctly.

 'Comp Ax - Rpt w/in 15bd' or 'Comp Ax - Rpt over 15bd' as assessment is associated with all 4 different intakes  from 'Psychology--Outpatient MH ' 

Yes, that's correct. 

 However, could there be a possibility of a patient's assessment leading to more than one intake?

Yes, this is a possibility - depending on the patient's needs. For example, in the example I provided above, ID 333 had' Comp Ax - Rpt over 15bd' for their assessment and then had 'Psychology Intake' and 'Psychosocial Intake' for their treatment intakes. So yes, there could be more than one intake, but never of the same kind (i.e., you won't see Psychology Intake twice). Also for their assessment, you won't see the same kind of assessment twice. For example, they either get 'Comp Ax - Rpt over 15bd' or 'Comp Ax - Rpt w/in 15bd' (this is just for us to track our business timelines because of our contract obligation with the contract holder. Basically, our target is to do the assessment within 15 business days, but sometimes that's not possible, hence, if it's over that target timeline, the clinician will enter Comp Ax - Rpt over 15bd' for comprehensive assessment).

Hope this clarifies things. Thanks so much!