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?