topic Re: Table Joins in Guide 5.1 in SAS Enterprise Guide

Table Joins in Guide 5.1

RickyS — Thu, 05 May 2016 13:07:01 GMT

Database desgn has a lookup table that I need to join against to translate values from the primary data tables. So in SQL I Would do this by:

Select b.value_desc as 'Status',

a.variable2,

a.variable3

From variable_table a

LEFT JOIN lookup b

on a.lookup_status= b.column_value and b.column_name = 'lookup_status'

Lookup Table

column_value	column_name	value_desc
1	lookup_status	Open
2	lookup_status	Closed
3	lookup_status	Pending

So if the value was 1 in the data table I would get back Open,when 2 Closed etc.

I have a bunch of work completed already in project mode and I know there is a way to do this in Guide using the query builder tables and joins in combination with an advanced expression, could not find an example in the forum.

Added Complexity

Within the data table there may 3 or 4 lookup values, can I do multiple joins to the lookup table for each lookup value? In SQL I'd have to rejoin to the lookup table for each variable against which I wanted value_desc returned.

Thanks

Re: Table Joins in Guide 5.1

Doc_Duke — Thu, 05 May 2016 14:05:56 GMT

Although you can do the lookups in SQL, do you really want the overhead of carrying those labels as part of a new data set? Have you considered using PROC FORMAT to label the results? You can use the contents of the lookup table to generate formats and then just link them when needed. There is an example of doing that in the PROC FORMAT documentation. It is also often quicker than the SQL-only aproach.

Re: Table Joins in Guide 5.1

RickyS — Thu, 05 May 2016 14:22:57 GMT

Proc Format is a possible solution and I have done that if and when the data table values are stable and I don't need to complete too many lookup joins.

For this data set some of the data values will be changing over time so I really do need to join to the tables to ensure the final output will always reflect the updated values.

Also, have been asked to build out in project mode so the less technical can gain a better perspective on the data aggregation through a conversation using the project window as the visualization tool.

Re: Table Joins in Guide 5.1

thorneton — Thu, 05 May 2016 14:32:12 GMT

Need some clarification regarding the variables in each table.The logic so far is: when a.lookup_status=1 and a.lookup_status=b.column_value and b.column_name='lookup_status' then 'Status'=b.value_description.

what do you mean when writing Within the data table there may be 3 or 4 lookup values? Which table a/b is the data table? What variable in which table may have 3 or 4 look values. Are these look values evaluated against another table.variable?

Re: Table Joins in Guide 5.1

RickyS — Thu, 05 May 2016 14:55:38 GMT

The database has around 100 primary data tables and 1 lookup table, each of the 100 data tables may have 20 - 30 variables of which 4 - 5 require translating by joining to the lookup table.

So you can use a Case statement in SQL, Proc Format in SAS to translate or Table join on the variable Name to the Lookup table to extract the translated value which is 'value_desc'. Pros and cons to each approach depending upon what you are doing.

Simplified Perspective of a Data Table

Variable Name

table_id

primary_key

secondary_id

lookup_status

lookup_type_of_client

lookup_client_company_structure

Lookup_client_business_volume

other_variable_1

other_variable_2

other_variable_3

other_variable_4

other_variable_5

other_variable_6

other_variable_7

other_variable_8

other_variable_9

other_variable_10

other_variable_11

other_variable_12

other_variable_13

other_variable_14

other_variable_15

other_variable_16

other_variable_17

other_variable_18

Re: Table Joins in Guide 5.1

ballardw — Thu, 05 May 2016 16:00:49 GMT

@RickyS wrote:

Proc Format is a possible solution and I have done that if and when the data table values are stable and I don't need to complete too many lookup joins.

For this data set some of the data values will be changing over time so I really do need to join to the tables to ensure the final output will always reflect the updated values.

Also, have been asked to build out in project mode so the less technical can gain a better perspective on the data aggregation through a conversation using the project window as the visualization tool.

I have a project that adds values at least monthly. I find it worth while to periodically examine the data and update formats, several are multilabel formats, because it makes the report writing later much easier. If these values change over time, who updates the lookup table(s) you would be using? An update to that table could trigger an update to the format.

Just a thought.

Re: Table Joins in Guide 5.1

RickyS — Wed, 11 May 2016 15:16:43 GMT

put a ticket in with tech support yesterday

Re: Table Joins in Guide 5.1

RickyS — Mon, 20 Jun 2016 13:20:23 GMT

Per usual the answer is pretty straight forward

QueryBuilder> Join Tables> Join Properties> Edit> code for the value you want returned

May vary slightly if your Options are not set to automatically choose the join variable