BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hatsumi
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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

6 REPLIES 6
Kurt_Bremser
Super User

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
Super User
Make a new variable for it.


new_SQ25=vvalue(SQ25);

hatsumi
Obsidian | Level 7

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
Opal | Level 21

@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
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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