Used join for the same tables multiple times, I want to limit the each table count to 1.

Discaboota — Tue, 09 Apr 2024 06:21:58 GMT

from RCA.VISIT_LOANACCOUNTS la
left join RCA.VISIT_FEEDBACK fb on Fb.Accountid=La.id 
left join RCA.VISIT_APPLICATIONORG ao on la.agencyid=ao.id 
left join RCA.VISIT_APPLICATIONUSER au on la.CollectorId=au.id 
left join RCA.VISIT_LOANACCOUNTS L on Fb.AccountId = L.Id
left join RCA.VISIT_APPLICATIONUSER auo on la.AllocationOwnerId=auo.id 
left join RCA.VISIT_APPLICATIONUSER auf on auf.id=fb.CreatedBy 
left join RCA.VISIT_APPLICATIONORG AOO on auf.BaseBranchId = AOO.Id

I have used left join for 4 tables by repeating them twice or thrice. I want to use each table once and want to put the joins, but when I do that it shows that the below table has not been referenced yet. How can I do that it error free?

Re: Used join for the same tables multiple times, I want to limit the each table count to 1.

yabwon — Tue, 09 Apr 2024 08:57:57 GMT

How the log look like? What is exact error message in the log?

Bart

[EDIT]

P.S. I'm asking about your log because in my sas session everything went smoothly:

code:

libname RCA (work);

data RCA.VISIT_LOANACCOUNTS;
  id=1;
  agencyid=1;
  CollectorId=1;
  AllocationOwnerId=1;
run;
data RCA.VISIT_FEEDBACK;
  Accountid=1;
  CreatedBy=1;
run;
data RCA.VISIT_APPLICATIONORG ;
  id =1;
run;
data RCA.VISIT_APPLICATIONUSER;
  id=1;
  BaseBranchId=1;
run;

proc sql feedback;
select * 
from RCA.VISIT_LOANACCOUNTS la
left join RCA.VISIT_FEEDBACK fb on Fb.Accountid=La.id 
left join RCA.VISIT_APPLICATIONORG ao on la.agencyid=ao.id 
left join RCA.VISIT_APPLICATIONUSER au on la.CollectorId=au.id 
left join RCA.VISIT_LOANACCOUNTS L on Fb.AccountId = L.Id
left join RCA.VISIT_APPLICATIONUSER auo on la.AllocationOwnerId=auo.id 
left join RCA.VISIT_APPLICATIONUSER auf on auf.id=fb.CreatedBy 
left join RCA.VISIT_APPLICATIONORG AOO on auf.BaseBranchId = AOO.Id 
;
quit;

log:

1    libname RCA (work);
NOTE: Libref RCA was successfully assigned as follows:
      Levels:           1
      Engine(1):        V9
      Physical Name(1): ************************************
2
3    data RCA.VISIT_LOANACCOUNTS;
4      id=1;
5      agencyid=1;
6      CollectorId=1;
7      AllocationOwnerId=1;
8    run;

NOTE: The data set RCA.VISIT_LOANACCOUNTS has 1 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


9    data RCA.VISIT_FEEDBACK;
10     Accountid=1;
11     CreatedBy=1;
12   run;

NOTE: The data set RCA.VISIT_FEEDBACK has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


13   data RCA.VISIT_APPLICATIONORG ;
14     id =1;
15   run;

NOTE: The data set RCA.VISIT_APPLICATIONORG has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


16   data RCA.VISIT_APPLICATIONUSER;
17     id=1;
18     BaseBranchId=1;
19   run;

NOTE: The data set RCA.VISIT_APPLICATIONUSER has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


20
21   proc sql feedback;
22   select *
23   from RCA.VISIT_LOANACCOUNTS la
24   left join RCA.VISIT_FEEDBACK fb on Fb.Accountid=La.id
25   left join RCA.VISIT_APPLICATIONORG ao on la.agencyid=ao.id
26   left join RCA.VISIT_APPLICATIONUSER au on la.CollectorId=au.id
27   left join RCA.VISIT_LOANACCOUNTS L on Fb.AccountId = L.Id
28   left join RCA.VISIT_APPLICATIONUSER auo on la.AllocationOwnerId=auo.id
29   left join RCA.VISIT_APPLICATIONUSER auf on auf.id=fb.CreatedBy
30   left join RCA.VISIT_APPLICATIONORG AOO on auf.BaseBranchId = AOO.Id
31   ;
NOTE: Statement transforms to:

        select LA.id, LA.agencyid, LA.CollectorId, LA.AllocationOwnerId, FB.Accountid, FB.CreatedBy, AO.id,
AU.id, AU.BaseBranchId, L.id, L.agencyid, L.CollectorId, L.AllocationOwnerId, AUO.id, AUO.BaseBranchId,
AUF.id, AUF.BaseBranchId, AOO.id
          from RCA.VISIT_LOANACCOUNTS LA left outer join RCA.VISIT_FEEDBACK FB on FB.Accountid = LA.id left
outer join RCA.VISIT_APPLICATIONORG AO on LA.agencyid = AO.id left outer join RCA.VISIT_APPLICATIONUSER AU
on LA.CollectorId = AU.id left outer join RCA.VISIT_LOANACCOUNTS L on FB.Accountid = L.id left outer join
RCA.VISIT_APPLICATIONUSER AUO on LA.AllocationOwnerId = AUO.id left outer join RCA.VISIT_APPLICATIONUSER
AUF on AUF.id = FB.CreatedBy left outer join RCA.VISIT_APPLICATIONORG AOO on AUF.BaseBranchId = AOO.id;

32   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds

topic Used join for the same tables multiple times, I want to limit the each table count to 1. in SAS Programming

Used join for the same tables multiple times, I want to limit the each table count to 1.

Re: Used join for the same tables multiple times, I want to limit the each table count to 1.