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.
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.
Haven't analysed your situation in detail, so this is just a general reflection. Visual data builder is a tool for quite simple use cases, based on SQL. If you have more complicated transformation requirements you may need a pre processed data mart created for you - which may be implemented in Base SAS code, DI Studio or a 3rd party ETL tool.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.