BookmarkSubscribeRSS Feed
dplessis
Calcite | Level 5

Hi,

 

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. 

 

 

2 REPLIES 2
Hentiedp
Fluorite | Level 6

Hi, 

 

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?

LinusH
Tourmaline | Level 20
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.
Data never sleeps

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!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 1457 views
  • 0 likes
  • 3 in conversation