BookmarkSubscribeRSS Feed
brulard
Pyrite | Level 9

hi,

 

When using the coalesce function, i'd like to know how to refer back to variable that I am coalescing, in terms of linking the coalesced variable, to the target right table (a.id=c.id in a typical join, but if a.id is now the coalesce(a.id,b.id), how to name this variable in the join criteria?

 

Similar question on how to name the 'order by' variable when this variable is  the one coalesced.

 

Thanks in advance,

 

 

 

 

4 REPLIES 4
ballardw
Super User

Since Join criteria involves comparing variables in two different tables (or at least aliases) the coalesce you are proposing can't exist before the join. Just because you have select a.id it does not mean that you are are replacing the ID value in table a. It would be going to the table on the Create table if you have one, if not it goes to the result view.

 

If you use a variable just created in a query then the predicate CALCULATED is used to indicate that you are referencing the just created value. So if named the coalesced value on the Select statement as C_id

 

order by calculated C_id

 

should work.

brulard
Pyrite | Level 9

Yeah, I had tried using Calculated but got error : "the following were not found as CALCULATED references in the immediate query".

 

Proc sql; create table b AS
SELECT DISTINCT 
         COALESCE(A.ID,B.ID) AS ACCT_ID,
         COALESCE(A.ACCT_NUM, B.ACCT_NUM) AS ACCT_NUM,
         A.PRD_CD AS PRODUCT,
         A.OPRTR_ID AS PTS,
         A.EVENT_SCRN_ID AS SCREEN,
         A.EVENT_DT AS DATE,
         A.TIME,
         A.EVENT_TP_CD AS CODE,
         A.FROM_EMPL_NAME,
         A.TO_EMPL_NAME,
         A.FROM_EMPL_ADDRESS,
         A.TO_EMPL_ADDRESS,
         B.FROM_JOB_TITLE,
         B.TO_JOB_TITLE,
         b.EVENT_TP_CD AS CODE_JOB_TITLE,
         B.time AS TIME_JOB_TITLE,
         b.oprtr_id AS PTS_JOB_TITLE,
         B.EVENT_SCRN_ID AS SCREEN_JOB_TITLE,
         C.crnt_bal_amt
FROM B0 AS A full
JOIN B1 AS b
    ON A.ACCT_NUM=B.ACCT_NUM
        AND A.EVENT_DT=B.EVENT_DT
        AND A.EVENT_TP_CD='055'
        AND B.EVENT_TP_CD='054'
INNER JOIN ice.snapshot C
    ON calculated acct_id=C.ID
        AND C.CUST_DATA_43=''; quit; 

I've highlighted the two lines of concern in green (the second line, and the one prior to the very last line). Should I instead make the join condition: on a.id or b.id=c.id?

 

brulard
Pyrite | Level 9
Ok, so I split the query into two. In the first segment, was able to use coalesce and end with "order by calculated ...". Still unsure why i am getting error when using calculated in the join criteria, but ultimately am getting the result i need after splitting my multiple joins.Thanks again
ballardw
Super User

@brulard wrote:
Ok, so I split the query into two. In the first segment, was able to use coalesce and end with "order by calculated ...". Still unsure why i am getting error when using calculated in the join criteria, but ultimately am getting the result i need after splitting my multiple joins.Thanks again

As I understand it any variable mentioned on the join must exist in one of the source tables or subqueries. You can apply functions to individual variables such as substr(a.var,2,4) but your Calculated value does not exist until after the join is completed in effect. 

So you get an error message that the calculated variable doesn't exist (yet).

 

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
  • 4 replies
  • 4237 views
  • 2 likes
  • 2 in conversation