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

Hi All,

 

i am working on loading SAS dataset (400 attributes)  into Oracle table (350 attributes).

 

I am using the proc append -  force nowarn in my code and still getting the Warning  for all of the records.

 

Is there any better suggestion than setting up the length for each individual attributes in the data step? (SAS variable naming is same as Oracle namings)

 

Thanks,

Ana

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

option VARLENCHK=NOWARN removes warnings when copying to SAS datasets.

 

So you could go through a SAS table or a SAS view.

 


data F_DATA.ORATAB; 
  VAR1='11';
run;
 
data SASTAB; 
  VAR1='11111111111111'; 
run;

option varlenchk=nowarn;

data TMP; 
  if 0 then set F_DATA.ORATAB; *use oracle lengths;
  set SASTAB; 
run;
                                               
proc append base=F_DATA.ORATAB data=TMP force nowarn; run;

 

 

View solution in original post

7 REPLIES 7
ChrisNZ
Tourmaline | Level 20

option VARLENCHK=NOWARN removes warnings when copying to SAS datasets.

 

So you could go through a SAS table or a SAS view.

 


data F_DATA.ORATAB; 
  VAR1='11';
run;
 
data SASTAB; 
  VAR1='11111111111111'; 
run;

option varlenchk=nowarn;

data TMP; 
  if 0 then set F_DATA.ORATAB; *use oracle lengths;
  set SASTAB; 
run;
                                               
proc append base=F_DATA.ORATAB data=TMP force nowarn; run;

 

 

SASAna
Quartz | Level 8

Thanks for the answer.  option VARLENCHK=NOWARN; didnt work on these warnings of the lengths.

 

Do we have to explicitly set the Oracle lengths to all the SAS variables? as I have around 350 variables to work with.

 

Thanks,

Ana

ChrisNZ
Tourmaline | Level 20

Did you even read my reply, or the documentation for this option?

 

This option works within SAS.

 

             
data F_DATA.ORATAB;
  VAR1='11';
run;
 
data SASTAB; 
  VAR1='11111111111111'; 
run;

proc append base=F_DATA.ORATAB data=SASTAB force nowarn; 
run; 

generates this warning: 

WARNING: Variable VAR1 has different lengths on BASE and DATA files (BASE 2 DATA 14).

 


option varlenchk=nowarn;

data TMP; 
  if 0 then set F_DATA.ORATAB; 
  set SASTAB; 
run;
                                               
proc append base=F_DATA.ORATAB data=TMP force nowarn; 
run;

generates no warning whatsoever.

 

Please at least *try* to help yourself.

 

 

 

 

SASAna
Quartz | Level 8
Thank you, All the warnings went off. Is there any option for "Variable has been defined as both character and numeric" error ? while loading into Oracle DB.

Thanks,
Ana
ChrisNZ
Tourmaline | Level 20

No there isn't. And there can't be. Is you sas numeric variable a date? or an integer?

You must have the variable types aligned.

 

SASAna
Quartz | Level 8

Hi,

 

Some of the attributes needs character to DATE conversations. and character to numeric conversions to load into DB.  I am currently doing all the needed conversions in PROC SQL.

 

Thanks,

Ana

ChrisNZ
Tourmaline | Level 20

Yes, that's one way.

While you're at it, ensure all the variables in all the programs and all the tables (SAS and Oracle) have the correct names, types and lengths. 

This will save you that kind of headache, and many others.

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

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 13765 views
  • 1 like
  • 2 in conversation