Help using Base SAS procedures

character to numeric conversion while sql extracting

Accepted Solution Solved
Reply
Super Contributor
Posts: 338
Accepted Solution

character to numeric conversion while sql extracting

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


Accepted Solutions
Solution
‎02-25-2014 01:11 PM
Super User
Posts: 19,770

Re: character to numeric conversion while sql extracting

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;

View solution in original post


All Replies
Super User
Posts: 5,424

Re: character to numeric conversion while sql extracting

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

, expression as column

leading to:

input(A_ACCTNUM, 18.) as A_Number

Data never sleeps
Super Contributor
Posts: 338

Re: character to numeric conversion while sql extracting

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.

Solution
‎02-25-2014 01:11 PM
Super User
Posts: 19,770

Re: character to numeric conversion while sql extracting

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;

Super Contributor
Posts: 338

Re: character to numeric conversion while sql extracting

Hi Reeza,

Thank you very much for your detailed clarification.

Hi LinusH,

Thank you very much for your help.

Regards

Miris

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 34674 views
  • 3 likes
  • 3 in conversation