BookmarkSubscribeRSS Feed
jacqelynl49
Calcite | Level 5

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;

15 REPLIES 15
Reeza
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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

Reeza
Super User

change input to put

EDIT: Sorry about that, its nap time now Smiley Happy

Patrick
Opal | Level 21

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
Calcite | Level 5

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
Opal | Level 21

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
Calcite | Level 5

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
Calcite | Level 5

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
Calcite | Level 5

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 2108 views
  • 6 likes
  • 4 in conversation