Date:         Tue, 3 Nov 1998 13:02:09 GMT
Reply-To:     jwalden@unconfigured.xvnews.domain
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         John Walden <jwalden@UNCONFIGURED.XVNEWS.DOMAIN>
Organization: The unconfigured xvnews people
Subject:      Re: Numeric to Char within SQL

There was a slight error in my post below.
The function for converting to a number
is "to_number()".  Someone privately
e-mailed me and pointed out that this wasn't
standard SQL.  I'm using Oracle version
7.2.3.0.0 on a Unix workstation, and SAS
release 6.12. I use these in the SQL passthrough
to the Oracle database.
 
I hope this didn't cause further confusion.
 
-John.
 
 
 
In article <71kb9q$8ri1@dilbert.whoi.edu>, jwalden@whsun1.wh.whoi.edu (John
Walden) writes:
>
> I've always found it easier to convert using the To_num, or
> to_char function in the select statement.
>
> select to_char(cmlssn) as ssn,
>
> this would convert cmssln if it's numeric to character.
>
>
> > The functions to use are PUT to convert a number to a string and INPUT to
> > convert a string to a number.  See below.
> >
> > <karljens@HOTMAIL.COM > wrote:
> >
> > >I have two datasets one has the variable defined a character, the other as
> > >numberic.  I want to join using these variables.  Is there a way to
convert
> > >the variable to a different type before testing equality?  EG:
> > >
> > >Proc SQL;
> > >  create table clm as
> > >        (select cmlssn as ssn,   defnam as fstnm, delnam as lstnm,
> > >                cmlpyd as pdamt, cmlpid as tin,   prpnam as prnam,
> > >                cmlpln as plan,  jepicd as servl,
> > >                substr(cmlpln,1,2) as pln,
> > >                substr(cmlpid,1,9) as pid
> > >           from Capsf.claims left join hailib.mbmdet on
cmlssn=CHAR(a0aqnb)
> >
> >  You would probably want to use
> >
> >    on cmlssn = put(a0aqnb, z9.)
> >
> > >          where cmlcdt between &begd and &endd
> > >            and substr(cmlpln,1,2) in ('TX','T1','CO','RI','NV','OK','WA')
> > >                );
> > >  quit;
>
> Best Regards,
>
> -John.
>
> --
> John Walden
> jwalden@whsun1.wh.whoi.edu
>
>
 
 
 
--
John Walden
jwalden@whsun1.wh.whoi.edu