topic Re: character to numeric conversion while sql extracting in SAS Procedures

character to numeric conversion while sql extracting

Mirisage — Tue, 25 Feb 2014 16:28:56 GMT

Hi SAS Forum,

1. The field named "A_number" in table named "wherehouse.EE_PP_AC_KDETAILS" is a character variable with length of 18

2. But the field named "S_ACCTNUM" in table named Work.bsba is a numeric

3. Q:How could I transform the "A_number" field to a numeric with the name of S_ACCTNUM in the code (red color portion)

Proc sql;

Create table temp as

Select a.*

,b.C_Limit

,b.A_number as S_ACCTNUM (S_ACCTNUM =Input(A_number, 18.)

From Work.bsba as a,

wherehouse.EE_PP_AC_KDETAILS as b

where (a.S_ACCTNUM = b.S_ACCTNUM);

quit;

Thank you for the help.

Miris

Re: character to numeric conversion while sql extracting

LinusH — Tue, 25 Feb 2014 16:49:01 GMT

Seems you are mixing SQL and other types of syntax. In SQL:

, expression as column

leading to:

input(A_ACCTNUM, 18.) as A_Number

Re: character to numeric conversion while sql extracting

Mirisage — Tue, 25 Feb 2014 17:12:49 GMT

Hi LinuH,

Many thanks.

I have changed the code like below using your logic but then I get the error messege in the log.

Could you please see what is the wrong.

Proc sql;

Create table temp as

Select a.*

,b.C_Limit

,input(b.A_number, 18.) as S_ACCTNUM

From Work.bsba as a,

wherehouse.EE_PP_AC_KDETAILS as b

where (a.S_ACCTNUM = b.S_ACCTNUM);

quit;

ERROR: Column S_ACCTNUM could not be found in the table/view identified with the correlation name B.

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

Re: character to numeric conversion while sql extracting

Reeza — Tue, 25 Feb 2014 18:11:28 GMT

Lets start from the top. You have two different variable names, but you've called them the same in your where clause. S_Acctnum won't exist in Table B so you should use A_number as that's what the variable is called.

Second, one is character and one is number, so you need to make them match. You can do that directly in the where statement.

Third, one of my pet peeves, you haven't specified a join type and are joining in the where clause. It's preferable to specify a join type and use a ON to join.

Proc sql;

Create table temp as

Select a.*

,b.C_Limit

From Work.bsba as a,

left join wherehouse.EE_PP_AC_KDETAILS as b

on (a.S_ACCTNUM = input(b.A_Number, 18.));

quit;

Re: character to numeric conversion while sql extracting

Mirisage — Wed, 26 Feb 2014 13:23:02 GMT

Hi Reeza,

Thank you very much for your detailed clarification.

Hi LinusH,

Thank you very much for your help.

Regards

Miris