BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I am trying to join two tables. One is the main data table and the other (from which I need one field), is relatively small. I am joining using a Left Join on two fields in the main table and three fields in the secondary table. To do this properly in Proc SQL, I would manually code this as follows:-
PROC SQL;
CREATE TABLE WORK.Query_for_CLGLEXT AS SELECT CLGLEXT.account_code,
CLGLEXT.accounting_date FORMAT=DDMMYY10.,
CLGLEXT.policy_number,
CLGLEXT.amount,
CLGLEXT.narrative,
CLGLEXT.generation_date FORMAT=DDMMYY10.,
CLGLEXT.class_code,
CLGLEXT.transaction_code,
CLGLEXT.batch_id,
CLGLEXT.cr_acc_no,
CLGLEXT.dr_acc_no,
CLGLEXT.agent,
CLGLEXT.last_acc_date FORMAT=DDMMYY10.,
CLGLEXT.renewal_date FORMAT=DDMMYY10.,
CLGLEXT.basic_prem_prop,
CLGLEXT.levy,
CLGLEXT.sfile,
CLASSIC_MAPPINGS.'GL Code'n
FROM CL_GLEXT.CLGLEXT AS CLGLEXT
LEFT JOIN CLGLPOST.CLASSIC_MAPPINGS AS CLASSIC_MAPPINGS ON
(CLGLEXT.account_code = CLASSIC_MAPPINGS.'Source Account'n) AND
(CLGLEXT.accounting_date >= CLASSIC_MAPPINGS.'Start Date'n) AND
(CLGLEXT.accounting_date <= CLASSIC_MAPPINGS.'End Date'n)
AND CLASSIC_MAPPINGS.Enabled = "*";
QUIT;

However, using the EG GUI, I am only able to do the following:-

PROC SQL;
CREATE TABLE WORK.Query_for_CLGLEXT AS SELECT CLGLEXT.account_code,
CLGLEXT.accounting_date FORMAT=DDMMYY10.,
CLGLEXT.policy_number,
CLGLEXT.amount,
CLGLEXT.narrative,
CLGLEXT.generation_date FORMAT=DDMMYY10.,
CLGLEXT.class_code,
CLGLEXT.transaction_code,
CLGLEXT.batch_id,
CLGLEXT.cr_acc_no,
CLGLEXT.dr_acc_no,
CLGLEXT.agent,
CLGLEXT.last_acc_date FORMAT=DDMMYY10.,
CLGLEXT.renewal_date FORMAT=DDMMYY10.,
CLGLEXT.basic_prem_prop,
CLGLEXT.levy,
CLGLEXT.sfile,
CLASSIC_MAPPINGS.'GL Code'n
FROM CL_GLEXT.CLGLEXT AS CLGLEXT
LEFT JOIN CLGLPOST.CLASSIC_MAPPINGS AS CLASSIC_MAPPINGS ON (CLGLEXT.account_code = CLASSIC_MAPPINGS.'Source Account'n) AND (CLGLEXT.accounting_date >= CLASSIC_MAPPINGS.'Start Date'n) AND (CLGLEXT.accounting_date <= CLASSIC_MAPPINGS.'End Date'n)
WHERE CLASSIC_MAPPINGS.Enabled = "*";
QUIT;


Why can't the GUI implement the additional test on the left join? I cannot see where to enable the condition that can be achieved in the manual version. Can anyone point me in the right direction, or can SAS take this as a bug in EG4.1?
1 REPLY 1
DaveShea
Lapis Lazuli | Level 10
Chris,

I am not sure if I am seeing the same problem that you do. The only difference I see in your two pieces of SQL are that your manually coded version contains the:

CLASSIC_MAPPINGS.Enabled = "*"

selection criterion as part of the ON whereas in the version generated by SAS/EG the same criterion is included as part of a stand-alone WHERE clause following the ON.

I would guess that in this case your actual results would be identical. Is this not the case for you ? If your results are not the same, what are the differences ?

Cheers.

Down-Under Dave.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 1 reply
  • 645 views
  • 0 likes
  • 2 in conversation