BookmarkSubscribeRSS Feed
Haris
Lapis Lazuli | Level 10

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.

5 REPLIES 5
Tom
Super User Tom
Super User

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
Lapis Lazuli | Level 10
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
Super User Tom
Super User

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
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10
DECODE() looks promissing. Thanks.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 907 views
  • 1 like
  • 2 in conversation