Date:         Fri, 14 Oct 2005 06:22:19 -0700
Reply-To:     Irin later <irinfigvam@YAHOO.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Irin later <irinfigvam@YAHOO.COM>
Subject:      SOS!!!  Failure to convert string to numeric keeping decimal and
              cleaning data
Content-Type: text/plain; charset=iso-8859-1

Hi,

Actually I tried the way Sig suggested before and I failed.

I run the SAS program in datamart within datawarehouse (DB2 SQL)
I am trying to convert string values under qu.res_val  field to numeric (keeping decimal
if any) and substituting all "dirty" data with 999

EXECUTE(
         create table &tmpschema..gsrv
                  (   Id                    char    (29)
                    , First_Srv_Dt     date
                    , rslt                 char (13)
                    , res_val            char (18)
                    , Num_Res_Val smallint
                  )
         in "&twotblspc"
         not logged initially
     ) by MYconnect;
     EXECUTE(
         insert into &tmpschema..gsrv
         select qu.Id
       , qu.dos
       , qu.rslt
       , qu.res_val,
    case
    when qu.res_val= '1+' then 2
    when qu.res_val='Invalid'   OR
            qu.lr_res_val_lit=' '
    then 999
    else coalesce(cast(nullif(qu.res_val,'') as decimal(16,2)),0)
  end AS Num_Res_Val
  from &clmschema..v_l_data                        qu
           join &tmpschema..num                      np
             on qu.Id               = np.Id

     ) by Myconnect;

1)When I use coalesce(cast(nullif(qu.res_val,'' ) as decimal(16,2)),0) I get:
****************************************************************************

55         then 999
156         else coalesce(cast(nullif(qu.res_val,'') as decimal(16,2)),0)
157          end AS Num_Res_Val
158          from &clmschema..v_l_data            qu
159             join &tmpschema..num                 np
160               on qu.Id                         = np.Id
161
162       ) by Myconnect;
ERROR: CLI execute error: [IBM][CLI Driver][DB2/AIX64] SQL0420N  Invalid character
found in a
       character string argument of the function "DECIMAL".  SQLSTATE=22018
163

2)When I use ISNUMERIC ()I get:
****************************************************************************
25         then 999
526         else isnumeric(qu.res_val)
527          end AS Num_Res_Val
528          from &clmschema..v_l_data            qu
529             join &tmpschema..num              np
530               on qu.Id               = np.Id
531
532       ) by Myconnect;

CLI execute error: [IBM][CLI Driver][DB2/AIX64] SQL0440N  No authorized routine named
       "ISNUMERIC" of type "FUNCTION" having compatible arguments was found.
SQLSTATE=42884


In the 2nd case it sounds like the function is not accepted by syntax
In the 1st case I am not sure if the function is accepted or not. If it is just a matter of some unrecognized invalid character then Why it does complains to DECIMAL????

Could you please help me as it is an urjent issue????

Thank you!

Irin




---------------------------------
 Yahoo! Music Unlimited - Access over 1 million songs. Try it free.