DATA Step, Macro, Functions and more

PROC Append - different lengths on BASE and DATA files (BASE 30 DATA 1).

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 123
Accepted Solution

PROC Append - different lengths on BASE and DATA files (BASE 30 DATA 1).

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


Accepted Solutions
Solution
‎01-31-2018 10:53 PM
PROC Star
Posts: 2,214

Re: PROC Append - different lengths on BASE and DATA files (BASE 30 DATA 1).

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


All Replies
Solution
‎01-31-2018 10:53 PM
PROC Star
Posts: 2,214

Re: PROC Append - different lengths on BASE and DATA files (BASE 30 DATA 1).

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;

 

 

Frequent Contributor
Posts: 123

Re: PROC Append - different lengths on BASE and DATA files (BASE 30 DATA 1).

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

PROC Star
Posts: 2,214

Re: PROC Append - different lengths on BASE and DATA files (BASE 30 DATA 1).

[ Edited ]

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.

 

 

 

 

Frequent Contributor
Posts: 123

Re: PROC Append - different lengths on BASE and DATA files (BASE 30 DATA 1).

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
PROC Star
Posts: 2,214

Re: PROC Append - different lengths on BASE and DATA files (BASE 30 DATA 1).

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.

 

Frequent Contributor
Posts: 123

Re: PROC Append - different lengths on BASE and DATA files (BASE 30 DATA 1).

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

PROC Star
Posts: 2,214

Re: PROC Append - different lengths on BASE and DATA files (BASE 30 DATA 1).

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.

☑ This topic is solved.

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

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