Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

How do I join two tables multiple times

Reply
New Contributor
Posts: 3

How do I join two tables multiple times

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. 

 

 

Occasional Contributor
Posts: 9

Re: How do I join two tables multiple times

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?

Esteemed Advisor
Posts: 5,081

Re: How do I join two tables multiple times

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
Post a Question
Discussion Stats
  • 2 replies
  • 261 views
  • 0 likes
  • 3 in conversation