BookmarkSubscribeRSS Feed
Discaboota
Obsidian | Level 7
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?

1 REPLY 1
yabwon
Onyx | Level 15

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
_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 354 views
  • 0 likes
  • 2 in conversation