I am looking for general guidance, references or specific suggestion on how I can see SAS formatted values in ORACLE.
The SOURCE dataset has many variables with numeric codes. In SAS each of these variables has a format that translates CODES to meaningful VALUES. Some users of this dataset don't have access to SAS. They view the data in ORACLE. All they see in ORACLE is coded values which are difficult to understand without formatted values.
How do I approach adding formatted values to ORACLE?
I've been thinking about creating an SQL VIEW script in ORACLE. Duplicating the dataset with formatted values does not seem reasonable. Any suggestions on how to automate the creation of this view? Obviously, I have a SAS format catalog with CODE to VALUE mapping for each variable. In ORACLE there is also a simple lookup data table with the following columns: VariableName, CodeValue, and FormattedValue. Here's a sample of the LOOKUP:
VariableName CodeValue FormattedValue
Sex 1 Male
Sex 2 Female
Race 1 White
Race 2 Black
Race 3 Asian
I could write separate joins with (where=(VariableName='XXX')) linking each variable in the source dataset to the LOOKUP but that seems terribly inefficient. There are about 100 variables to be labeled. Here's an example of what I am trying to avoid:
create view Source_V as
select ID,
Sex, sx.FormattedValue as Sex_VAL,
Race, r.FormattedValue as Race_VAL
from Source s
left join LookUp(where=(VariableName='Sex')) sx ON s.Sex=sx.CodeValue
left join LookUp(where=(VariableName='Race')) r ON s.Sex=r.CodeValue
I don't even know if ORACLE will understand the WHERE clause. I may need to write an entire sub-query for each joint.
Appreciate any guidance.
Why not just make the new variables BEFORE loading the data into Oracle?
libname xxx oracle .... ;
data xxx.have_with_decodes;
set have ;
sex_decode = vvalue(sex);
race_decode = vvalue(race);
run;
In that case you probably need to ask your question on an Oracle forum. There are special hints you can put into Oracle code to help it understand how to efficiently do joins. I have seen them in Oracle code people send me to adapt to SAS. Perhaps your Oracle database admin can suggest something for you.
For small code lists perhaps you just want to build DECODE() functions into your views. But for dictionary terms you probable will want to use actual table joins.
Here's some progress. This code automates the production of syntax for the VIEW. Rather than multiple JOINTS, I am creating CASE WHEN THEN syntax that will work with no joints at all:
data _NULL_;
set LOOKUP;
by Variable_Name;
if First.Variable_Name=1 then
put Variable_Name +(-1) ','
/ 'case ' Variable_Name
/ @5 'when ' Value 'then ' '"'Code_Text+(-1)'"'; else if First.Variable_Name=0 AND Last.Variable_Name=0 then
put @5 'when ' Value 'then ' '"'Code_Text+(-1)'"'; else if Last.Variable_Name=1 then
put @5 'when ' Value 'then ' '"'Code_Text+(-1)'"'
/ @3 'end AS ' Variable_Name +(-1) '_VAL,';
run;
/* Statements like this are produced by this syntax:
case VarName1
when 1 then "FValue1"
when 2 then "FValue2"
when 3 then "FValue3"
when 4 then "FValue4"
when 5 then "FValue5"
when 6 then "FValue6"
when 7 then "FValue7"
when 8 then "FValue8"
end AS VarName1_VAL,
case VarName2 ...
*/
This code generates SQL code and writes it to the log. From there, it's copy and paste, add the SELECT and FROM statements, add variables that are not formatted and deploy.
`+(-1)` code removes blanks that are otherwise automatically inserted by the PUT statement between variable values and text.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.