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?
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.