JOINING OF TABLES

[Sultana]

libname Autoload 'F:\sasdata\data\autoload\report;
proc sql ;
create table test as
Select
 b.NAICSCode ,
 b.Callreportcode ,
 b.Loannumber 
 b.Name as ,
 a.Currentprincipalbalance ,
 a.Lineavailable as ,
 sum(a.Currentprincipalbalance,a.Lineavailable) as Commitment_Amount format=comma15.2,
 c.Officername,
 e.BranchMarketDescription,
f.AccountNumber,
g.QATPostingDate,
g.SICCodeValue81
 
from Autoload.NEW_Loan_Balance a
left join Autoload.DIM_LOAN b on a.Accountkey=b.Accountkey
left join Autoload.DIM_OFFICER c on b.OfficerKey=c.Officerkey
left join Autoload.DIM_BRANCH_COST_CENTER d on b.branchcostcenterkey=d.branchcostcenterkey
left join Autoload.DIM_BRANCH_MARKET e on e.BRANCHMARKETKEY=d.BRANCHMARKETKEY
INNER JOIN AUTOLOAD.DIM_ACCOUNT f ON g.AccountNumber = g.AccountNumber);
QUIT;
where
    
  SICCode55=Y
   ;
quit;

I had to add  match the LOANSPECIALINFOCODEHISTORYWIDE table (where QATPostingDate = “YYYY-MM-DD” (last business day of whatever month you are using) and SICCodeValue55=Y) and join to DIM_ACCOUNT by AccountNumber to get the AccountKey.  Then you could match to NEW_LOAN_BALANCE by AccountKey to get the correct loans.  

[SuryaKiran]

It is not so clear what your trying to do here, please try to provide a sample data and your required result.

If your trying to run the same query you posted then make sure you have the quotes for the file path in the Libname statement and remove the quit before where clause.

[PeterClemmensen]

Can you show us an example of what your data looks like? Makes it much easier to help you.

[José_Costa_biw]

So, first of all, you have several syntax errors:

1. No closing comma on the libname statement;

2. No comma after b.Loannumber;

3. No alias set for b.Name and a.Lineavailable;

4. "INNER JOIN AUTOLOAD.DIM_ACCOUNT f ON g.AccountNumber = g.AccountNumber" --> this whole line is strange as you're trying to make an inner join after several left joins (good luck controlling that!) of the table aliased as f but on a variable of table g (which does not exist in your query) with itself (!)

5. Speaking of table aliased as g, despite not existing in your query, you're trying to get two variables (g.QATPostingDate, g.SICCodeValue81)) of it! And you're ending that line with a closing parenthesis that doesn't have a "companion" opening anywhere in your query.

6. The QUIT; before the where can't be there.

So, first solve all these issues, and then post clearly what is missing from your requirements.