Date:         Thu, 25 Aug 2011 02:39:33 -0400
Reply-To:     Søren Lassen <s.lassen@POST.TELE.DK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Søren Lassen <s.lassen@POST.TELE.DK>
Subject:      Re: Urgent help needed! Oracle table numeric variable format
              length showing one byte more in SAS?
Comments: To: Haikuo Bian <HBian@NW7.ESRD.NET>
Content-Type: text/plain; charset=ISO-8859-1

Haikuo,
I think the reason SAS adds one to the Oracle numeric length when creating
the format is that Oracle specifies the length of numbers as number of
digits, not including the sign, whereas SAS includes the sign in the
length of the format. Not that much consolation, I know, but at least there
is a good reason.

Regards,
Søren
On Tue, 23 Aug 2011 14:28:15 -0400, Bian, Haikuo <HBian@NW7.ESRD.NET> wrote:

>To follow up:
>
>A Birdie send me the following link:
>http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/view
er.htm#a003113620.htm
>
>Basically, that is what SAS does: add ONE to Oracle numeric variable
format. So I guess there is nothing I can do within SAS.
>
>Regards,
>Haikou
>
>-----Original Message-----
>From: Bian, Haikuo
>Sent: Monday, August 22, 2011 12:22 PM
>To: Bian, Haikuo; 'SAS-L@listserv.uga.edu'
>Subject: RE: Urgent help needed! Oracle table numeric variable format
length showing one byte more in SAS?
>
>One thing to add, proc contents does not save me on this one either. This
problem seems trivial, but it is part of contract detailed in QC procedure,
so I have to do it one way or the other.
>
>Thanks,
>Haikuo
>
>-----Original Message-----
>From: Bian, Haikuo
>Sent: Monday, August 22, 2011 12:09 PM
>To: SAS-L@listserv.uga.edu
>Subject: Urgent help needed! Oracle table numeric variable format length
showing one byte more in SAS?
>
>Dear List,
>
>Recently I have run into this scenario:
>
>There are some Oracle tables that I need to process in SAS, and after I
have done a describe using proc sql, all of the numeric variable increase
their format (and informat) length by one byte:
>
>15   proc sql;
>16   describe table oracle.whateveroracletable;
>NOTE: SQL table oracle.whateveroracletable was created like:
>
>create table oracle.whateveroracletable (
>   NEW_DATA num format=2. informat=2. label='NEW_DATA'
>  );
>
>17   quit;
>
>
>For this new_data variable, it should be like: " format=1. Informat=1.",
which I can obtain by using SQL plus (showing number(1)), so I know it
hasn't changed.
>
>It is much easier to do it in SAS, but if I report my log back to upstream
Oracle production team, they will be freaking out.
>
>I have tried both Libref and sql pass-through, they both gave me the same
results.
>
>My specs: Windows xp sp4, SAS 9.2
>
>Please help!
>
>
>Thanks,
>Haikuo