Date:         Wed, 3 Oct 2007 15:31:27 -0400
Reply-To:     Samuel Croker <samuel.croker@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Samuel Croker <samuel.croker@GMAIL.COM>
Subject:      Re: I tried to load integer data into db2 through proc sql
In-Reply-To:  <CA8F89971ADA9F47A6C915BA2397844207B42009@MAILBE2.westat.com>
Content-Type: text/plain; charset=ISO-8859-1

Have you considered writing the create table statement explicitly
before loading?  It might not be as flexible but has the advantage of
being exact. You would probably want to do this in pass through SQL
using the pass through facility.

proc sql;
   connect to db2 (database=<<tablename>>);
   execute (<<create table statement>>) by db2;
   disconnect from db2;
quit;


Sam


On 10/3/07, Sigurd Hermansen <HERMANS1@westat.com> wrote:
> If you are loading data from a SAS WORK Library dataset TEST into a db2
> table T_recipients_tbl, I would expect to see a LIBNAME prefix to
> T_recipients_tbl.  In that case I'd say a SAS FORMAT would have the best
> chance of coaxing db2 into converting a SAS real number type to a db2
> integer type. I assume that you are using the SAS/Access db2 engine as
> the middleware that connects SAS to db2. The SAS/Access documentation
> may indicate whether the engine can handle a type conversion to integer
> or not.
>
> Note that SAS does not have an integer data type, and that, while a SAS
> FORMAT presents (in this case) a real number as an integer, it does not
> convert the value.
>
> You might also try
>
>     ... SELECT input(put(transaction_id,10.),10.), ....
>
>    to force the db2 engine to convert a string of digits from real to
> integer type. The PUT() function converts a real to a digit string and
> the INPUT() function converts the digit string to a number using an
> INFORMAT (e.g., 10.). Neither function creates a variable of integer
> type, though, so all depends on what the engine actually does.
> S
>
> -----Original Message-----
> From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu]
> On Behalf Of Kuladeep Mittapalli
> Sent: Wednesday, October 03, 2007 1:12 PM
> To: SAS-L@listserv.uga.edu
> Subject: I tried to load integer data into db2 through proc sql
>
>
> Hi,
> When i tried to load integer data into db2 through proc sql,After
> loading data into db2,it converts into double.
>
>
> I want to load integer data only.would you please help me out in this
> critical situation .
>
>
> Thanks
> My code looks below :
>
> *
>
> PROC* *SQL* NOPRINT;
>
> CREATE TABLE T_recipients_tbl(BULKLOAD=YES) AS
>
> SELECT transaction_id FORMAT *10.*,
>
>         program_id FORMAT *10.*
>
> **FROM TEST;
> *
>
> QUIT*;
>
> %*runstats*(tbl_name=T_recipients_tbl);
>


--
Samuel T. Croker
Lexington, SC & Bethesda, MD