How to identify unique recipients of 5 classes of psychotropic meds

[wlierman]

Hello,

I have a question pertaining to how to identify unique recipients (by recipient id) across 5 classes of psychotropic meds for two years (2022 and 2023) of quarterly data.

The set up is as follows:

                   med1  med2 med3 med4  med5                     med1  med2  med3  med4  med5

 Q1 2022                                                                          Q1 2023

Q2 2022                                                                           Q2 2023

Q3 2022                                                                           Q3 2023

Q4 2022                                                                           Q4 2023

The steps that I follow:

1] vertically combine the quarters into CY 2022 and 2023.

2]starting with med1 as the 'base' med check across each of the 5 med types to

    identify only unique recipients. The data requestor only wants to count each

    recipient once across the med classes.

3] So the final output would give the following for all recipients of both years

2022       med1                  med2              med3             med4           med5

           unique #             unique #           ... and so on ...........    

           of recipient       of recipients

and then the same for 2023.   The output would be to spread all unique recipients across the 5 classes of meds for both years.

I originally thought of an sql approach, having seen sql solutions on stack overflow blogs. 

Expert solutions would be appreciated.

Thank you.

Walt Lierman

[ballardw]

This will probably go much quicker if you provide an example what your current data looks like. The values don't have to be exact, just behave the same. Since your question involves "unique recipients" that means there has to be some way of identifying the recipient as well as the meds and apparently date information.

Then from the example show what the result should look like.

Example data is best provided as data step code that we can run pasted into a text box opened on this forum using the </> icon that appears above the message window.

A brief possible example:

data have;
  input patientid $   date :date9. med $;
  format date date9.;
datalines;
1111   01Jan2022   abc
1111   05Feb2022   pdq
2222   04Jan2022   xyzanol
;

I would recommend for the example to use dummy medication names as above. Actual names can have issues besides length and how much typing would be involved. You question involves 5 meds but you could likely do with 3 for the example. Then show how you expect the report to run for your provided example data.

[wlierman]

Sounds reasonable.  Will take a little bit.
Thanks.

#- Please type your reply above this line. No attachments. -##

[mkeintz]

If your data does not have a recipient ID variable, how do you propose to identify unique recipient?  Is each pattern of MED1 through MED5 unique to only one individual?

[wlierman]

My oversight: there is a recipient ID on each record.  The data was retrieved from a datawarehouse and duplicates could be checked vertically through each month of the quarter - some other preprocessing steps were also applied. 

The requestor wants unique recipients across each med type.  So this deduplication is across the five meds in a horizontal direction.
I will try to provide some data from the dataset.  It will take some time to avoid sending potentially sensitive information.

[ballardw]

If my previous example data step has elements similar to your data then just make stuff up like that. Just provide a few records and as I mentioned before likely only need 3 "med" values to play with. Likely 15 to 20 lines of data should be sufficient to get started as long as you show what the expected result for those would be. Please make sure that not every value in the output example has the same count, it should be possible to see that pretty easily for as small of an example as I suggest.

Some patients should not have some of the "med" values.

[wlierman]

That sounds good.  There will be something ready to go tomorrow.Thank you.