SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

SAS data to Oracle DB - label is lost in newly created Oracle dataset

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

SAS data to Oracle DB - label is lost in newly created Oracle dataset

[ Edited ]

Hi 

 

I converted SPSS survey data to SAS dataset. Then I moved this SAS dataset to Oracle DB and realized that labels were lost during the move.

 

 

proc sql;
create table Oracle.data as 
select * from work.data;
quit;

 

When I converted SPSS file to SAS dataset, labels were still there. For example, data table showed like below.

SQ24 SQ25
Nancy Like
Kevin Dislike
Chris Like

 

However, when I moved SAS dataset from temp work file to Oracle DB, then labels were lost during conversion and only the underlying values were transferred like below.

SQ24 SQ25
Nancy 1
Kevin 0
Chris 1

 

Is there anyway I can keep the original label values in Oracle DB? Otherwise, I need to do a lot of coding to do re-mapping of values to labels...

 

Thanks!


Accepted Solutions
Solution
‎08-24-2017 09:14 PM
Respected Advisor
Posts: 4,678

Re: SAS data to Oracle DB - label is lost in newly created Oracle dataset

[ Edited ]

@hatsumi

Just use an intermediary data step.

data work.table2;
  set work.table(rename=(SQ25=SQ25_Orig));
  SQ25=vvalue(SQ25_Orig);
  drop SQ25_Orig;
run;

proc sql;
  create table Oracle.data as 
  select * 
  from work.table2
  ;
quit;

 

View solution in original post


All Replies
Super User
Posts: 9,890

Re: SAS data to Oracle DB - label is lost in newly created Oracle dataset

It's most probably that you have the same display formats in SAS that you have in SPSS, but are missing them in Oracle, so your raw values are displayed.

Note that "label" in SAS speek is an attribute of a column or a dataset. What you mean here is called a "format".

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,689

Re: SAS data to Oracle DB - label is lost in newly created Oracle dataset

Make a new variable for it.


new_SQ25=vvalue(SQ25);

Super User
Posts: 9,890

Re: SAS data to Oracle DB - label is lost in newly created Oracle dataset


Ksharp wrote:
Make a new variable for it.


new_SQ25=vvalue(SQ25);


And once again I learn of a useful function I didn't know of. I love this forum.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 35

Re: SAS data to Oracle DB - label is lost in newly created Oracle dataset

Hi Ksharp, thanks for your example code. I tested it, but then I got a new error message saying below.

 

data Oracle.table;
set work.table;
new_SQ25=vvalue(SQ25);
run;

ERROR: Error attempting to CREATE a DBMS table. ERROR: ORACLE execute error: ORA-00904: : invalid identifier..

 

I didn't have this error when I had the original SQL code like below, so conversion needs to go through SQL method I think.

proc sql;
create table Oracle.table 
from work.table;
quit;

Because data step method creates this error message.. 

Data Oracle.table;
Set work.table;
run;

ERROR: Error attempting to CREATE a DBMS table. ERROR: ORACLE execute error: ORA-00904: : invalid identifier..

 

and vvalue is not recognized in SQL I think as below error message suggests.

proc sql;
create table Oracle.table as
select vvalue(SQ25) as new_SQ25 
from work.table;
quit;

ERROR: The VVALUE function is not supported in PROC SQL, it is only valid within the DATA step.

 

Do you have any other alternative ways to keep the original format using SQL query?

Solution
‎08-24-2017 09:14 PM
Respected Advisor
Posts: 4,678

Re: SAS data to Oracle DB - label is lost in newly created Oracle dataset

[ Edited ]

@hatsumi

Just use an intermediary data step.

data work.table2;
  set work.table(rename=(SQ25=SQ25_Orig));
  SQ25=vvalue(SQ25_Orig);
  drop SQ25_Orig;
run;

proc sql;
  create table Oracle.data as 
  select * 
  from work.table2
  ;
quit;

 

Contributor
Posts: 35

Re: SAS data to Oracle DB - label is lost in newly created Oracle dataset

Sorry for my late response, but thank you for your help! My table produced error with your suggested code, but I think it's my table's issue.. It doesn't make sense that my table doesn't accept data step but it runs with SQL without any error ... I'm sure your code will be useful to others and next time I encounter similar issue using different SPSS file. Thanks again!! Smiley Very Happy

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 669 views
  • 7 likes
  • 4 in conversation