Date:         Wed, 9 Dec 2009 23:37:19 +0200
Reply-To:     nina <sas@MAILINATOR.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         nina <sas@MAILINATOR.COM>
Organization: A noiseless patient Spider
Subject:      help with a sybase proc sql
Comments: To: sas-l@uga.edu
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
              reply-type=original

normally I read a 14 digit numeric value residing in havevalue (defined as
value1 bigint) and this example below works fine, matching my small lookup
table "havevalue" from "bigtable".

the havevalue table variable value1 may contain leading zeros.

proc sql;
 connect to sybase (user=xxx pass=xxx server=xxx);
 create table x as select * ( select * from bigtable a, havevalue b
 where a.value1 = b.value1  );

the above works fine, no problem.

problem is that I have another table, "largetable" and it breaks the 14
digit numeric value into 3 separate fields where 1 is character and 2 are
numeric

value1_part1 = character and contains always 10 digits, may or may not have
leading zeros
value1_part2 = numeric and contains always 2 digits, may or may not have
leading zero as 1st digit
value1_part3 = numeric and contains always 1 digits

keep in mind that "havevalue" value1 may contain leading zeros.

I was trying to get the data by the following code and would appreciate any
feedback and corrections

proc sql;
 connect to sybase (user=xxx pass=xxx server=xxx);
 create table x as select * ( select * from largetable a, havevalue b
 where
 cast(a.value1_part1 as varchar(10)) || cast(a.value1_part2 as varchar(2))
|| cast(a.value1_part3 as varchar(1))
    =  cast(  b.value1 as varchar(14)  );