topic Re: Using new created fields in PROC Sql - Joins in SAS Programming

Using new created fields in PROC Sql - Joins

deleted_user — Thu, 24 Jun 2010 09:44:07 GMT

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_

Re: Using new created fields in PROC Sql - Joins

Patrick — Thu, 24 Jun 2010 12:17:33 GMT

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

Re: Using new created fields in PROC Sql - Joins

deleted_user — Thu, 24 Jun 2010 16:58:27 GMT

example of sql will help figure out what is issue

Re: Using new created fields in PROC Sql - Joins

Ksharp — Fri, 25 Jun 2010 04:27:13 GMT

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.

Re: Using new created fields in PROC Sql - Joins

Peter_C — Fri, 25 Jun 2010 08:02:22 GMT

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

Re: Using new created fields in PROC Sql - Joins

Ksharp — Fri, 25 Jun 2010 10:07:45 GMT

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.

Re: Using new created fields in PROC Sql - Joins

Peter_C — Fri, 25 Jun 2010 13:05:32 GMT

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 😉

Re: Using new created fields in PROC Sql - Joins

Ksharp — Sat, 26 Jun 2010 07:25:40 GMT

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