Date:         Wed, 24 Mar 2004 07:57:06 -0500
Reply-To:     harry.droogendyk@RBC.COM
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Harry Droogendyk <harry.droogendyk@RBC.COM>
Subject:      Re: SQL variable manipulation
Content-Type: text/plain; charset=iso-8859-1

Untested:

If you are using a pass-thru query, you can use Oracle functions to
accomplish this:

proc sql;
  connect to oracle (  blah blah );

  create table libname.sasds as
    select * from connection to oracle
      (  select flda
               ,lpad(char_var,6,'0')
           from oracle_table
      )
   ;
   disconnect from oracle;
quit;

If you're using the Oracle libname to read the data, you'll have to use SAS
functions:

data libname.sasds;
  set oralib.oracle_table ( keep = flda char_var );
  char_var = put(input(char_var,6.),z6.);
run;


-----Original Message-----
From: Dave Foot [mailto:footd@NORWICH-UNION.CO.UK]
Sent: Wednesday, March 24, 2004 5:29 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SQL variable manipulation


I'm reading a Oracle table that has a character variable. It holds
numeric values only. What I want to do is read that value, covert to
show leading zeroes, then store it back to a new SAS dataset as a
character value,

i.e. read in 493 - write out 000493.

I want to do this in one SQL step as I'm using a DBCONDITION. I
realise I could do this in a datastep, but I want to prevent reading
the data all over again.

------------------------------------------------------------
This e-mail may be privileged and/or confidential, and the sender does not
waive any related rights and obligations. Any distribution, use or copying of
this e-mail or the information it contains by other than an intended recipient
is unauthorized. If you received this e-mail in error, please advise me (by
return e-mail or otherwise) immediately.

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce
pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation
ou copie de ce message ou des renseignements qu'il contient par une personne
autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez
ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par
retour de courrier électronique ou par un autre moyen.

============================================================