BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RickyS
Quartz | Level 8

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_valuecolumn_namevalue_desc
1lookup_statusOpen
2lookup_statusClosed
3lookup_statusPending

 

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   

1 ACCEPTED SOLUTION

Accepted Solutions
RickyS
Quartz | Level 8

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  

View solution in original post

7 REPLIES 7
Doc_Duke
Rhodochrosite | Level 12

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.

RickyS
Quartz | Level 8

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.

ballardw
Super User

@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.

thorneton
Fluorite | Level 6

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?

RickyS
Quartz | Level 8

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

 

 

 

 

RickyS
Quartz | Level 8

put a ticket in with tech support yesterday

RickyS
Quartz | Level 8

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  

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1393 views
  • 0 likes
  • 4 in conversation