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
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
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.
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.
@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.
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?
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 |
put a ticket in with tech support yesterday
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.