topic Re: JOINING OF TABLES in SAS Enterprise Guide

JOINING OF TABLES

Sultana — Thu, 15 Nov 2018 16:53:20 GMT

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.

Re: JOINING OF TABLES

SuryaKiran — Thu, 15 Nov 2018 17:16:41 GMT

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.

Re: JOINING OF TABLES

PeterClemmensen — Thu, 15 Nov 2018 17:17:27 GMT

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

Re: JOINING OF TABLES

José_Costa_biw — Thu, 15 Nov 2018 17:45:43 GMT

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.