comparing varchar(13) loan number to numeric (10,) loan number

[jacqelynl49]

Hi

The below are just examples to use to ask my question.

I am trying to change the below proc sql using ODBC to the one below that due to other fields created in previous code.

The loan_num and loan_no are being compared in the final query but proc contents of each table shows:

LOAN_NO Num 8 11. 11.

Loan_Num Char 13 $13. $13.

I have been unable to research and find the syntax to convert the numeric loan_no to character or visa versa.  Any help much appreciated!

proc sql;
connect to odbc (noprompt="Driver={SQL Server};
                      Server=THIS IS INTENTIONALLY LEFT BLANK FOR PRIVACY REASONS;
                     database=daisy001");

create table test2 as select * from connection to odbc
(select m1.loan_num, cast(lc.loan_no as varchar) from cpidata.dbo.tbl14 lc
left join daisy001.dbo.master1 m1 on m1.loan_num = lc.loan_no
where m1.loan_num = lc.loan_no
);quit;

I want to code as this:

proc sql;

create table test1 as
(select m1.loan_num, lc.loan_no from PAPCPI.tbl14 lc
left join PAPDSY1.master1 m1 on m1.loan_num = lc.loan_no
where m1.loan_num = lc.loan_no
);quit;

[Reeza]

Use the INPUT function to convert character to numeric

https://ciser.cornell.edu/faq/sas/char2num.shtml

You may need to trim loan_num because of the trailing spaces to get a match. 

If you have leading zeros you may want to use Z8, instead of 8 below.

proc sql;

create table test1 as
(select m1.loan_num, lc.loan_no from PAPCPI.tbl14 lc
left join PAPDSY1.master1 m1 on m1.loan_num = lc.loan_no
where trim(m1.loan_num) = input(lc.loan_no, 8. -l)
);quit;

[jacqelynl49]

Thank you for your quick response.

I ran your suggestion and received this error:

ERROR: Expression using equals (=) has components that are of different data types.

ERROR: INPUT function requires a character argument.

[Reeza]

Right, I didn't look closely at your code, you have a ON and WHERE and you only need one, preferably the ON

This assumes that loan_num is CHAR, and loan_no is Numeric as indicated above.

proc sql;

create table test1 as
select m1.loan_num, lc.loan_no from PAPCPI.tbl14 lc
left join PAPDSY1.master1 m1
ON trim(m1.loan_num) = input(lc.loan_no, 8. -l)
;quit;

[jacqelynl49]

Still getting the input function requires a character argument.  Loan_no is numeric

[Reeza]

change input to put

EDIT: Sorry about that, its nap time now

[Patrick]

You either need both variables as numeric or both as character. I would go for character to allow for the already existing character variable to have values other than digits.

on left(m1.loan_num) = put(lc.loan_no, 13.)   

Why do you want to change the explicit SQL to an implicit one? Using an explicit SQL has the advantage that you're under full control what SQL gets sent to the data base for execution. With implicit SQL using the SAS SQL flavor there is a good chance that a SAS function can't get translated into a data base function which results in SAS first loading all the data into SAS and only then applying the functions.

In your case: "put(lc.loan_no, 13.)" won't get translated so SAS will first have to load both tables into SAS and only then apply the where clause.

A search like below will show you which SAS functions can get translated for your data base "site:support.sas.com 9.4 passing SAS functions to <your data base>".

If using implicit SQL as you want to then make sure that you also use option OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX; as this will show you in the SAS log what part of the SQL SAS was able to send to the data base.

[jacqelynl49]

I mentioned in the original post that we are using this implicit sql because there are other tables we have created previously that need to be joined into this query.

This is just a small snippet of the entire query as this is showing the exact problem.

Thank you!

[Patrick]

Still, try to reduce volumes on the database side as much as possible before you "suck" the data into SAS - unless the volumes are so low that it doesn't matter and you prefer to go for simple code.

Else: The code snippet I've posted should work. With SAS you need a PUT() function to cast from numeric to character.

[jacqelynl49]

Well it was running so I thought we were going to be okay but no luck.  No I have no idea what is happening.

Reeza: When I run your code I get back NOTHING in the loan_num column and the loan_no(s) are anywhere from 2 to 8 digits which means the leading zeros in loan_no were dropped.

Loan_Num LOAN_NO
                                  75
                                 208
                                 521
                                 950
                                1115
                                1131

  through to
                      508353265

proc sql;

create table test3 as

select m1.loan_num, lc.loan_no from PAPCPI.tbl14 lc

left join PAPDSY1.master1 m1 ON trim(m1.loan_num) = put(lc.loan_no, Z8. -l)

;

quit;

Patrick:  Your suggestion returns the same as Reeza's.

proc sql;

create table test3 as

select m1.loan_num, lc.loan_no from PAPCPI.tbl14 lc

left join PAPDSY1.master1 m1 ON left(m1.loan_num) = put(lc.loan_no, 13.)   

;

quit;

:smileyconfused:

[jacqelynl49]

I got the loan_no(s) to show correctly by using this: strip(put(lc.loan_no,10.))  Do you know what is causing the blank column?

[Zugati]

In your Join try this:  Left join daisy001.dbo.master1 m1 on LTrim(m1.loan_num,0) = lc.loan_no

LTrim will remove leading zeroes from loan_num.  Syntax is LTrim(column name,character value)

I hope this helps you.

[jacqelynl49]

Sorry - didn't say thank you for your suggestion..

ERROR: Function LTRIM could not be located.

ERROR: Expression using equals (=) has components that are of different data types.

[Reeza]

Try the following, if it doesn't work, post samples of your data, both columns

proc sql;

create table test3 as

select trim(m1.loan_num) as loan_num, put(lc.loan_no, Z8. -l) as loan_no

from PAPCPI.tbl14 lc

left join PAPDSY1.master1 m1 ON

trim(m1.loan_num) = put(lc.loan_no, Z8. -l)

;

quit;

[jacqelynl49]

NOTE: Table WORK.TEST4 created, with 476272 rows and 2 columns.

NOTE: At least one W.D format was too small for the number to be printed. The decimal may be

      shifted by the "BEST" format.

NOTE: PROCEDURE SQL used (Total process time):

      real time           4:19.91

      cpu time            6.63 seconds

column two is correct with the new query but column one is still blank

Can't post actual information.  Numbers look like this in their correct format - 0012345678 and 12345678

Thank you all for your continued assistance.