BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tmcrouse
Calcite | Level 5

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
MEMdosmpinADMIT_DTDISCH_DTprov_mpin_pcinat_ctnat_cathnat_petnat_perfofcinpatoutpat
1111/16/201210453411
1111/16/20129074441
1112/20/2012623256111
1112/20/20126234222/20/20122/21/201211
1116/6/2012104534111
1116/6/20126234226/6/20126/7/201211
1116/28/2012623256111
1116/28/20126234226/28/20126/29/201211
1118/31/201210453411
2221/16/201210453411
2221/16/20129074441
2222/20/2012623256111
2222/20/20126234222/20/20122/21/201211
2226/6/2012104534111
2226/6/20126234226/6/20126/7/201211
2226/28/2012623256111
2226/28/20126234226/28/20126/29/20121
2228/31/201210453411

DATA NEED
MEMmpinofcctipctopctofccathipcathopcathofcpetippetoppetofcperfipperfopperf
1111045322
1116234222
1116232561
22210453211
22262342211
2226232561
1 ACCEPTED SOLUTION

Accepted Solutions
Vince28_Statcan
Quartz | Level 8

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?

View solution in original post

5 REPLIES 5
Amir
PROC Star

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
Calcite | Level 5

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
Quartz | Level 8

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
Quartz | Level 8

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
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1063 views
  • 0 likes
  • 3 in conversation