Code Help

[tmcrouse]

I have a table that I am trying to create a data step on. I have tried multiple ways to code this but it does not quite work out the way I want to. I am posting the data I have in the table and how I need it to look in the end. Just in case it does not paste right, I attached the spreadsheet. The goal is to say after the patient has a PCI procedured, if a ct, cath, pet or perf are done between 1 and 90 days after the PCI, then count that procedure but only on the original MPIN that performed the PCI. So, MPIN 104534 has a pci on 6/6/2012 and then 6/28/2012 MPIN 623256 does an outpat cath and on 8/31/2012 the original MPIN does an outpat cath. So, that counts as 2 in the outpat cath column for MPIN 104534. Now, the below is just an example of the DATA HAVE. I have 27k rows that make up 15k MPINS. So, I know that there has to be a do loop in my code until the very last MPIN is analyzed within the data.

DATA HAVE
MEM	dos	mpin	ADMIT_DT	DISCH_DT	prov_mpin_pci	nat_ct	nat_cath	nat_pet	nat_perf	ofc	inpat	outpat
111	1/16/2012	104534							1	1
111	1/16/2012	907444								1
111	2/20/2012	623256			1		1					1
111	2/20/2012	623422	2/20/2012	2/21/2012	1							1
111	6/6/2012	104534			1		1					1
111	6/6/2012	623422	6/6/2012	6/7/2012	1							1
111	6/28/2012	623256			1		1					1
111	6/28/2012	623422	6/28/2012	6/29/2012	1							1
111	8/31/2012	104534					1					1
222	1/16/2012	104534							1	1
222	1/16/2012	907444								1
222	2/20/2012	623256			1		1					1
222	2/20/2012	623422	2/20/2012	2/21/2012	1							1
222	6/6/2012	104534			1			1			1
222	6/6/2012	623422	6/6/2012	6/7/2012	1							1
222	6/28/2012	623256			1				1	1
222	6/28/2012	623422	6/28/2012	6/29/2012	1
222	8/31/2012	104534				1					1

DATA NEED
MEM	mpin	ofcct	ipct	opct	ofccath	ipcath	opcath	ofcpet	ippet	oppet	ofcperf	ipperf	opperf
111	104532						2
111	623422						2
111	623256						1
222	104532		1								1
222	623422		1								1
222	623256		1

[Amir]

Hi,

Maybe I'm missing something, but I am struggling to understand some of the example data in light of the given rules. Please clarify:

1) How do the column name in NEED relate to the columns in HAVE. For example, I can't see which column ipct comes from and how it is calculated.

2) I assume columns in NEED starting "in" and "op" relate to "inpat" and "outpat" in HAVE. What is the rule for which "in" or "out" column in NEED should be populated?

3) I assume 104532 in NEED is just a typo of 104534 in HAVE.

Regards,

Amir.

[tmcrouse]

1) The HAVE is the data I HAVE. The NEED is what I NEED for an output.

2) The HAVE for ofc relates to ofc in the NEED. The inpat in HAVE relates to the ip in NEED and the oppat in HAVE relates to the op in NEED.

3) The HAVE data shows a provider performing a PCI on a specific DOS. So, let's say DOS is 6/6/2012. 3 days later the same or different provider performs a cath, perf, pet or ct and that service is performed let's say op, or outpatient. Then 15 days later the same or different provider performs a cath, perf, pet or ct that is ip or inpatient. Then the same or different provider performs a cath, perf, pet or ct 30 days later that is ofc or office. For example purposes, I will say the 3 days later an op cath is performed. 15 days later an ip pet is performed and 30 days later an ofc perf is performed. The provider that performed the original PCI is 104532, and yes that would be typo ending in the 4. So, this means that 104532 would have a count of:

1 opcath

1 ippet

1 ofcperf

Anytime a provider does a PCI, the dos is viewed and if any of those procedures are performed between 1 and 90 days after the PCI, the original provider gets the follow-up procedures that are performed.

Hard to explain all of this and type fast to get this project done. I have 1 day to finish and probably not going to achieve because I have posted this same thing 4 times now with so many views and no help. I suspect no one knows how to code it, therefore I will just stick with code I have for now and manually calculate what I need until I figure out how to code it. Not sure what else to do. Even the SAS team at SAS themselves are at a loss. Truly sad since my org pays extra money for coding help. Not talking tech support, but actual coding help.

[Vince28_Statcan]

I'm affraid the problem at hand is slightly too vague for the community to appropriately understand what you want to program.

I'm trying to go by your example above but I am not entirely sure what you ought to do. In particular, I struggle to understand how exactly you define the initial date of service for each given mem/mpin. That is, at first glance, I assumed that the initial DoS was the first occurence of prov_mpin_pci=1 but then if that's the case, 111/623442 should show as 0 OPCATH and not 2.

Similarly, since you ought to count services provided by any provider within 90 days of each providers' first service, why is the opcath on 8/31/2012 not contributing (within 90 days) of 111/104532 in your desired output? Is there a requirement for services to only be counted if prov_mpin_pci=1?

Also, if 2 mem/mpin get to have their initial date of service the same day, can they contribute to the count of each other or is it only sequentially to the current time series ordering?

If you can provide a clearly detailed conditioning rationale to your counting, I might be able to help. I have a few ideas on how to handle the vertical processing but I can't figure out exactly how you condition your count based on the information you've provided.

Vincent

P.S. I don't work in the medical field like many other communities users. If there is an underlying medical rationale to what you are trying to achieve that is self-explanatory, you would need to provide further details on your specific medical context/what the variables fully refer to etc.

[Vince28_Statcan · Accepted solution]

So I figured that you did not count the service provided on "day 0" or initial date of service. Still, I struggle to figure out how you would handle cases where the same mem/mpin would have different non-zero counts over different 90 days period intervals.

That is, in your example, both 623422 and 623256 mpins had a service starting on 2/20/2012. Now, in the example, they each had 0 other services within 1-90 days. However, had any service occured, what would've been your expected output? The total sum of all 90 days interval following a service with no overlap? The total of the longest rolling interval? (think the rolling interval for 111/623256 should've extended to 90 days after 6/28/2012 even though the initial DoS was 6/6/2012) The first or last 90 days period to occur?

[tmcrouse]

Thanks for everyones input. I figured out how to do what I needed to do. When I am done QAing the data, I will post my solution here if anyone ever needs some complex code. At least it seems complex to me.