Want to see SAS Formatted Values in ORACLE

[Haris]

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.

[Tom]

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;

[Haris]

We don't want to store decoded values in ORACLE. Some of the variables are diagnoses. Both diagnosis CODE and Description (formatted value) are needed but descriptions are so much longer than the codes. Many patients have the same diagnosis so storing long descriptions in the database will balloon the database.

[Tom]

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.

[Haris]

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.

[Haris]

DECODE() looks promissing. Thanks.