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

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

SASAna — Thu, 01 Feb 2018 00:42:45 GMT

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

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

ChrisNZ — Thu, 01 Feb 2018 02:33:01 GMT

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;

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

SASAna — Thu, 01 Feb 2018 02:53:49 GMT

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

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

ChrisNZ — Thu, 01 Feb 2018 03:00:14 GMT

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.

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

SASAna — Thu, 01 Feb 2018 03:55:41 GMT

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

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

ChrisNZ — Thu, 01 Feb 2018 04:14:29 GMT

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.

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

SASAna — Thu, 01 Feb 2018 19:08:22 GMT

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

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

ChrisNZ — Thu, 01 Feb 2018 22:29:37 GMT

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.