character to numeric conversion while sql extracting

[Mirisage]

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

[LinusH]

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

, expression as column

leading to:

input(A_ACCTNUM, 18.) as A_Number

[Mirisage]

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.

[Reeza · Accepted solution]

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;

[Mirisage]

Hi Reeza,

Thank you very much for your detailed clarification.

Hi LinusH,

Thank you very much for your help.

Regards

Miris