06-01-2016 01:14 AM
I am a new user to SAS VA (convert from Qliksense) and am having some trouble with data preparation tool and the most efficient way to create a base query / table for reporting. I have a two pronged questions.
I have two tables:
Table 1has a large volume of hospital admission data including the hospitals, specialist and doctor provider numbers
Table 2 holds the information for providers (hospitals, doctors, specialists) using a department of health provider number (unique Identifier) and contains their discription, addresses etc.
The issue is that I have to link table 2 too multiple fields in table 1. A single row in table one will contain a provider number for up to three different hosptals columns (treating hospital, hospital transfered from (often blank) and hospital transfered too (often blank). Like wise there will be two medical providers for each row as two seperate columns. All of these providers reference to the Health provider number column (unique Identifier) in table 2.
What is the best and most efficient way to do this?
Currently I have pulled table two in multiple times (which SAS VA automatically creating alias tables i guess for each instance) and then joining on multiple fields. This is causing long processing times and I have received issues with running out of utility space to process the query.
Assistance would be greatly appreciated as I am sure there is an efficient approach that I am missing.
06-01-2016 05:17 AM
Can you perhaps share what code you currently have?
The most effective methods really depends. PROC SQL is sometimes the most effective and some times a plain data set merge. (Depending on a lot of factors - mostly what you need to accomplish). When I've got complex joining of data I tend to lean towards PROC SQL. Something that I've found can influence performance is query design.
Also, what is the current processing time?
06-01-2016 06:46 PM