Joining tables on multiple keys (name AND date)

Dennis_Arrindel — Mon, 13 Jul 2020 19:54:07 GMT

Hi there SAS Community,

I am a relatively new user to SAS Visual Analytics 7.3 and trying to make some useful reports for the organization I work at. The organization is a health insurer that pays physicians for their services. I have already read a lot about SAS VA 7.3 and can find my way around the software.

The specific report I want to make has the following characteristics (detailed example data can be found in the link at the end of this post).

Table 1 has information about referrals from one physician to another healthcare provider.

Table 2 has information about the how many patients are assigned to one physician. The physicians are paid according to a subscription model. This is called 'capitation' in healthcare, because the physician received a fixed amount of money 'per capita' (patient) assigned, regardless of whether this patient seeks care or not.

What I am trying to do is get an indication about how 'trigger happy' physician's are when it comes to referring patients to other healthcare providers.

For example, if in the year 2019 physician A has had a total of 1000 patients assigned to him, and has referred 100 of these, I report that as "10% of the the patient's assigned to physician A get referred". This information is used as input for various comparisons between physicians and is something that I used to do in Excel.

This is the approach I tried but that is not working for me:

Join table 1 'referrals' and table 2 'capitation' on two join keys, namely 'Physician name' (in reality this is a unique number) and 'date'.

Here I am running in to 2 problems.

(1) First of all, I can't seem to get a table join to work on more than 1 join condition. I know this is possible in other programs I have worked with, such as Google Big Query SQL.

(2) Second, the dates from referral table and capitation table should not match exactly (which is never the case), but should match in period intervals: for example a year. Meaning all referrals done by physician A in 2019 and all total amount of distinct count patients assigned to physician A during 2019. So possibly I need to use a Group By Function in data preparation and aggregate the dates by year or so (I haven't been able to get this to work yet).

The user of this report should be able to choose between different periods, for example 2017 until 2019, or only quarter 2 of 2018. In each case, the date column of both tables needs to move with whatever period the user wants to see. Drill down here is not the priority: If I can get the report to work on a year to year basis alone it's already useful enough to be distributed.

The external consultants that are the suppliers of SAS to my organization have made such a report, so I know it is possible. It is however a report that I would like to be able to create myself. The solution to this problem will also enable me to make a lot of similar reports, as we have a lot of reports where I need to do table joins between two keys, on of which is a 'time dimension'.

Please see the example data in this google sheet file:

https://docs.google.com/spreadsheets/d/1JqMxlZYFzzPDNqbRNp0Q0WSe5m4L_wvldCXufFeyR3k/edit?usp=sharing

I am looking forward to you help in this regard, thank you in advance!

topic Joining tables on multiple keys (name AND date) in SAS Visual Analytics

Joining tables on multiple keys (name AND date)