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

[hatsumi]

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!

[Kurt_Bremser]

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".

[Ksharp]

Make a new variable for it.


new_SQ25=vvalue(SQ25);

[Kurt_Bremser]

---

@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.

[hatsumi]

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?

[Patrick · Accepted solution]

@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;

[hatsumi]

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!!