sas proc sql and using a condition for the left join

[alepage]

Asking the expert!

I would like to make multiple left join in order to create a new table.

One of my table is SoumEnLigne as table8

In this table, I have the variable primaryidentityclass which could take only two values (quote or account)

Therefore based on the value of this variable I would like to make two different left join

ex:

case 1, primaryidentityclass=quote then

on table1.POLICYNUMBER = table8.primaryentityid and
datepart(table8.started)<=table1.dttrnsys<=sum(datepart(table8.started),7) and table1.type_lot="QT";

Case2, primaryidentityclass=account then

n table1.ACCOUNTNUMBER = table8.primaryentityid and
datepart(table8.started)<=table1.dttrnsys<=sum(datepart(table8.started),7) and table1.type_lot="QT";

So if the primaryidentityclass eq quote the we compare the policynumber of table1 with the primaryidentityid of table8 

if the primaryidentityclass eq account the we compare the accountnumber of table1 with the primaryidentityid of table8 

How can we do that ?

Here's a part of my log file

53 on table1.POLICYNUMBER = table8.primaryentityid and
54 datepart(table8.started)<=table1.dttrnsys<=sum(datepart(table8.started),7) and table1.type_lot="QT";
55 %end;
56
57 %if table8.primaryentityclass eq "Account" %then
58 %do;
59
60 on table1.ACCOUNTNUMBER = table8.primaryentityid and
61 datepart(table8.started)<=table1.dttrnsys<=sum(datepart(table8.started),7) and table1.type_lot="QT";
62
63 %end;
64 ;
65 QUIT;
66 %mend provbqt1;
67 %provbqt1;
MLOGIC(PROVBQT1): Début de l'exécution.

MPRINT(PROVBQT1): proc sql;
MLOGIC(PROVBQT1) : La condition %IF table8.primaryentityclass eq "Quote" est FALSE
MLOGIC(PROVBQT1) : La condition %IF table8.primaryentityclass eq "Account" est FALSE
NOTE: Line generated by the invoked macro "PROVBQT1".
67 ;
_
73
76
MPRINT(PROVBQT1): create table provnbqt1 as select table1.*, table5.dtcreationVIP, table5.hrcreationVIP, table5.VIP,
table6.clicassure, datepart(table6.started) as dtcreationCLA format yymmdd10., timepart(table6.started) as hrcreationCLA format
time8., table7.rentecoll, datepart(table7.started) as dtcreationRCO format yymmdd10., timepart(table7.started) as hrcreationRCO
format time8., table8.web FROM provnbqt as table1 left join ref_vip as table5 on table1.ACCOUNTNUMBER = table5.ACCOUNT_NUMBER and
table1.dttrnsys = table5.dtcreationVIP left join clicassure as table6 on table1.ACCOUNTNUMBER = table6.primaryentityid and
datepart(table6.started)<=table1.dttrnsys<=sum(datepart(table6.started),7) and table1.type_lot="QT" left join rente_coll as table7
on table1.ACCOUNTNUMBER = table7.primaryentityid and datepart(table7.started)<=table1.dttrnsys<=sum(datepart(table7.started),7) and
table1.type_lot="QT" left join SoumEnLigne as table8 ;
ERROR 73-322: Expecting an ON.

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
MPRINT(PROVBQT1): QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):

[RW9]

Why are you over complicating it?  You want to select xyz variable from have2 based on one merge, and abc variable from have2 based on second merge:

proc sql;
  select A.*, 
         B.B_QUOTE,
         B.XYZ,
         C.ACCOUNT,
         C.ABC,
         case when A.POLICY_NUMBER=B.B_QUOTE then "Cond1" else "" end as FLAG1,
         case when A.ACCOUNT_NUMBER=C.PRIMARY_ENTITY_ID then "Cond2" else "" end as FLAG2
  from   HAVE A
  left join (select MERGEVARS,QUOTE as B_QUOTE,XYZ from HAVE) B
  on        A.MERGEVARS=B.MERGEVARS
  left join (select MERGEVARS,ACCOUNT as C_ACCOUNT,ABC from HAVE) C
  on        A.MERGEVARS=B.MERGEVARS;
quit;

So your effectively creating variables for what is in the joins rather than trying to conditionally merge.  Also, you would most likely get a better response to questions, by providing test data in the form of a datastep, an what you want to see at the end.  Showing a small snippet of some code with macros and what not going on does not help anyone, neither does a mass of cODe wH%cH lOKKS L%ke thiS.

[alepage · Accepted solution]

I am not sure I understand what your code is doing.

I have tryed two consecutive join as below.  What do you think about that?

proc sql;
create table provnbqt1
as select table1.*, table5.dtcreationVIP, table5.hrcreationVIP, table5.VIP,
table6.clicassure, datepart(table6.started) as dtcreationCLA format yymmdd10., timepart(table6.started) as hrcreationCLA format time8.,
table7.rentecoll, datepart(table7.started) as dtcreationRCO format yymmdd10., timepart(table7.started) as hrcreationRCO format time8.,
table8.web

FROM provnbqt as table1

left join ref_vip as table5
on table1.ACCOUNTNUMBER = table5.ACCOUNT_NUMBER and table1.dttrnsys = table5.dtcreationVIP

/*201607 CP je veux ajouter la provenace clic assure la note est au niveau account et on a un delai de 7 jours
admissible on associe que les soum on va passer par une provenance tempo pour les ventes*/
left join clicassure as table6
on table1.ACCOUNTNUMBER = table6.primaryentityid and datepart(table6.started)<=table1.dttrnsys<=sum(datepart(table6.started),7)/* and table1.type_lot="QT"*/

/*201609 CP je veux ajouter la provenace rente collective la note est au niveau account et on a un delai de 7 jours
admissible on associe que les soum on va passer par une provenance tempo pour les ventes*/
left join rente_coll as table7
on table1.ACCOUNTNUMBER = table7.primaryentityid and datepart(table7.started)<=table1.dttrnsys<=sum(datepart(table7.started),7) /*and table1.type_lot="QT"*/

left join SoumEnLigne (where=( PRIMARYENTITYCLASS eq "Quote")) as table8
on table1.POLICYNUMBER = table8.primaryentityid and
datepart(table8.started)<=table1.dttrnsys<=sum(datepart(table8.started),7)

left join SoumEnLigne (where =(PRIMARYENTITYCLASS eq "Account")) as table9
on table1.ACCOUNTNUMBER = table9.primaryentityid and
datepart(table9.started)<=table1.dttrnsys<=sum(datepart(table9.started),7) /*and table1.type_lot="QT"*/

;
QUIT;