Date: Tue, 3 Nov 1998 13:02:09 GMT
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
188.8.131.52.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.
In article <email@example.com>, firstname.lastname@example.org (John
> 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
> > >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
> > 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 Walden