topic To join multiple tables in SAS EG about 5 tables in SAS Enterprise Guide

To join multiple tables in SAS EG about 5 tables

Sultana — Fri, 02 Nov 2018 20:10:25 GMT

To join multiple tables in SAS EG about 5 tables the primary key is different in some so cannot join. The report is prepared in excel and have to replicate it but the fields are set up in different tables. Can you show me a query where I can perform joins, where the primary key is missing in some cannot join them.

Re: To join multiple tables in SAS EG about 5 tables

Reeza — Fri, 02 Nov 2018 20:19:03 GMT

@Sultana wrote:

To join multiple tables in SAS EG about 5 tables the primary key is different in some so cannot join. The report is prepared in excel and have to replicate it but the fields are set up in different tables. Can you show me a query where I can perform joins, where the primary key is missing in some cannot join them.

Can you post some examples of how you want that to be handled? If the key isn't there what record gets matched? Or what gets included. Please show what you have as starting data and what you want as output and explain the logic, then we can either suggest an approach or code. But without more information this is a vague question, which gets a vague answer.

To include data please see:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Re: To join multiple tables in SAS EG about 5 tables

Sultana — Mon, 05 Nov 2018 20:46:04 GMT

Well the Tables are Market- marketdescription
Branch -Branch Number
officer- OfficerName
loan-Customer number,
customername
account -Accountnumber
Fact Loan-original
balance,Adjusted Balance, Dayspast due.
Probability-Probabilityof
Defaultcode

These are the 7 VA tables and the eight table have to take from the source
table from which Pastdue is required.

Now Factloan and loan has common field as the account key
and Account and Factloan has the common field Account key.

Branch and loan has branch has common field Branch key.

this code
PROC SQL;
CREATE TABLE WORK.QUERY_FOR_DIM_LOAN AS
SELECT t1.CustomerNumber,
t1.CustomerIBCifNumber,
t2.AdjustedBalance,
t2.DaysPastDue,
t2.OriginalBalance,
t3.AccountNumber
FROM TMP00001.dim_loan t1, TMP00001.fact_loan_balance t2,
TMP00001.dim_account t3
WHERE (t1.AccountKey = t2.AccountKey AND t1.AccountKey =
t3.AccountKey);
QUIT;
Gives me the result for the selected ones.
This I do with SASEG query but if I do with left join,
libname Autoload 'F:\sasdata\data\autoload\report_mart_auto_load';
proc sql ;
create table Test as
Select
a.OriginalBalance,
a.DaysPastDue,
a.AdjustedBalance,
b.LoanNumber,
c.Officername,
a.timeid
from Autoload.Fact_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
where a.timeid = '20160200';
QUIT;

The result is
[image: image.png]

Please let me know how to move on
I need to get \
marketdescription BranchNumbe
OfficerName CustomerIBCifNumber
AccountNumber CustomerName OriginalBalance
AdjustedBalance ProbabilityOfDefaultCode
DaysPastDue Past Due

In my final dataset from all the dataset mentioned above.

Regards,
Sultana

Re: To join multiple tables in SAS EG about 5 tables

Reeza — Mon, 05 Nov 2018 21:11:53 GMT

No images were included. If you want more fields, add them to the SELECT list.

It seems like you do have key variables and not seeing what the issue given what's been posted so far. It may be easier to use the EG query builder where you can see how the tables are joined and control the fields and joins more easily, especially if you understand the data. Then look at the generated code and pull that into your process if desired.

Re: To join multiple tables in SAS EG about 5 tables

Sultana — Mon, 05 Nov 2018 21:18:04 GMT

I do not have the common variables for market , branch and officer.

Re: To join multiple tables in SAS EG about 5 tables

Reeza — Mon, 05 Nov 2018 21:19:04 GMT

You have to have a common variable, though they may not have the same name.

Re: To join multiple tables in SAS EG about 5 tables

Sultana — Mon, 05 Nov 2018 21:22:04 GMT

Can you write a query for me for the tables as a sample which I can refer
to as like I did not understand *though they may not have the same name*

Re: To join multiple tables in SAS EG about 5 tables

Reeza — Mon, 05 Nov 2018 21:31:43 GMT

I don't know the columns or relationships so that's literally impossible. But I'm guessing a key in one table may be something like Branch Code vs Branch Number in another table. It sounds like you're working with a relational DB so you're looking for what may be called foreign keys to the DBs.

Re: To join multiple tables in SAS EG about 5 tables

TomKari — Mon, 05 Nov 2018 23:16:03 GMT

Before you get into a complex query, perhaps a simple example might clarify things a bit.

Here's a simple one-to-many join that shows the idea.

Tom

data Customers;
length CustomerID 8 CustomerName $20 ContactName $20 Address $30 City $15 PostalCode $10 Country $10;
input CustomerID & CustomerName & ContactName & Address & City & PostalCode & Country &;
cards;
1  Alfreds Futterkiste  Maria Anders  Obere Str. 57  Berlin  12209  Germany
2  Ana Trujillo Emparedados y helados  Ana Trujillo  Avda. de la Constitución 2222  México D.F.  05021  Mexico
3  Antonio Moreno Taquería  Antonio Moreno  Mataderos 2312  México D.F.  05023  Mexico
run;

data Orders;
informat OrderDate yymmdd10.;
input OrderID CustomerNumber EmployeeID OrderDate ShipperID;
cards;
10308  2  7  1996-09-18  3
10309  37  3  1996-09-19  1
10310  77  8  1996-09-20  2
10311  2  9  1996-09-21  4
run;

proc sql noprint;
create table OrdersAndCustomers as
select Orders.OrderID, Orders.OrderDate, Customers.CustomerID, Customers.CustomerName
from Orders inner join Customers on Orders.CustomerNumber = Customers.CustomerID;
quit;