Using new created fields in PROC Sql - Joins

[deleted_user]

Hi everybody,

I'm actually trying to create a new field in the SELECT-Statement by using the CASE-Function and referring to this field in the join-condition (e.g. join a left join b
on a.x = calculated new_field).
But somehow it doesn't work, maybe it's because I'm restricted to using the "calculated"-fields only in SELECT- and WHERE-clauses.
Does anybody have an idea how to solve this problem?

Kind Regards,
Chris_

[Patrick]

What I normally do in such cases is to first create a view and then join the view with the second table.

SQL would sure also allow other approaches - but this approach is easy to read for me.

Just to give an example:

proc sql;
create view V_TblA as
select substr(a,3) as key
from TblA
;
select r.*
from V_TblA as l left join TblB as r
on l.key=r.key
;
quit;

HTH
Patrick

[deleted_user]

example of sql will help figure out what is issue

[Ksharp]

Hi.
I agree with Patrick.You need two sql statments.
The first one to create your needed dataset by calculated variable.
The second one to left jion.

[Peter_C]

sounds like establishing the calculated column in a preliminary view, is the only way with base SAS.
It would be interesting to review the combined query after it passes through the optimiser.
Would the SASTRACE system option, or the _TREE and _METHOD proc sql options help?
Are there other ways to explain the implementation of a query?

peterC

[Ksharp]

Hi. Peter
I think you are more excellent than me, have more knowledge about sas than me .
So I have no idea about it. Also I have a little confusion with implementation of a query.

[Peter_C]

a useful paper which started me learning how sql works is given at http://support.sas.com/techsup/technote/ts553.html "TS-553 SQL Joins -- The Long and The Short of It"
It's a really good paper, but if I'm still asking questions, I guess I'm still learning 😉

[Ksharp]

Thx. Peter.
I will take a look at it.
Actually I am a beginner of SAS , so there are so many things to learned.



Regards

Ksharp