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
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;
Seems you are mixing SQL and other types of syntax. In SQL:
, expression as column
leading to:
input(A_ACCTNUM, 18.) as A_Number
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.
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;
Hi Reeza,
Thank you very much for your detailed clarification.
Hi LinusH,
Thank you very much for your help.
Regards
Miris
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.