DATA Step, Macro, Functions and more

Using new created fields in PROC Sql - Joins

Reply
N/A
Posts: 0

Using new created fields in PROC Sql - Joins

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_
Respected Advisor
Posts: 3,899

Re: Using new created fields in PROC Sql - Joins

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
N/A
Posts: 0

Re: Using new created fields in PROC Sql - Joins

example of sql will help figure out what is issue
Super User
Posts: 9,687

Re: Using new created fields in PROC Sql - Joins

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.
Valued Guide
Posts: 2,175

Re: Using new created fields in PROC Sql - Joins

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
Super User
Posts: 9,687

Re: Using new created fields in PROC Sql - Joins

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.
Valued Guide
Posts: 2,175

Re: Using new created fields in PROC Sql - Joins

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 ;-)
Super User
Posts: 9,687

Re: Using new created fields in PROC Sql - Joins

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
Ask a Question
Discussion stats
  • 7 replies
  • 919 views
  • 0 likes
  • 4 in conversation