BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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_
7 REPLIES 7
Patrick
Opal | Level 21
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
Not applicable
example of sql will help figure out what is issue
Ksharp
Super User
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
Rhodochrosite | Level 12
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
Super User
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
Rhodochrosite | Level 12
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
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 5553 views
  • 0 likes
  • 4 in conversation