Two Table Join Exercises

Reply
New Contributor
Posts: 3

Two Table Join Exercises

[ Edited ]

In the data warehouse, we are receiving transactions from multiple source systems on a regular basis. Below are the sample datasets from one of the transaction system,

 

AccountId

CountryCode

RelationshipOfficer

CurrencyCode

ProductCode

TransactionDate

Amount

ApprovedBy

MonthendDate

111

SG

5016

SGD

LON

10/11/2016

10,000.00

7015

31/3/2017

111

HK

6011

HKD

OVD

20/3/2017

5,000.00

8214

31/3/2017

111

SG

5016

SGD

LON

10/11/2016

2,000.00

7015

31/3/2017

111

SG

5016

SGD

LON

20/10/2016

8,000.00

7015

30/3/2017

111

SG

5016

SGD

LON

8/7/2016

9,000.00

7015

31/3/2017

111

HK

6011

HKD

OVD

12/10/2016

6,000.00

8214

31/3/2017

222

UK

3014

GBP

OVD

2/1/2017

7,000.00

6011

31/3/2017

222

UK

3014

GBP

OVD

31/12/2016

6,500.00

6011

31/3/2017

222

UK

3014

GBP

OVD

7/11/2016

6,000.00

6011

31/3/2017

222

UK

3014

GBP

LON

31/12/2016

4,000.00

6011

31/3/2017

SourceCurrency

TargetCurrency

ExchangeRate

SGD

USD

0.73

HKD

USD

0.13

GBP

USD

1.32

SGD

EUR

0.64

HKD

EUR

0.11

 

 

You are given the task to write code to generate below output.

 

AccountId

CountryCode

ProductCode

CurrencyCode

Amount

MonthendDate

111

SG

LON

USD

8,760.00

31/3/2017

111

HK

OVD

USD

650.00

31/3/2017

222

UK

OVD

USD

9,240.00

31/3/2017

222

UK

LON

USD

5,280.00

31/3/2017

 

Hints:

  • Pick the latest transactions for the combination of AccountId, CountryCode and ProductCode.

  • If there are multiple transactions for same date and combination, we should add them up.

  • Convert the amount to USD using the currency rate table provided.

 

Super User
Posts: 9,868

Re: Two Table Join Exercises

[ Edited ]

You're supposed to do your homework yourself. Post what you already tried, and where you run into problems.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 3

Two Table Join Exercises, #2

[ Edited ]

Customer and scorecard systems are sending regular feeds to data warehouse. Below the sample datasets for customer and scorecard data,

 

Customer

 

CustomerId

DomicileCountryCode

IncorporationCountryCode

ParentCustomerId

CustomerType

A1111

SG

 

X1111

BANK

B2222

HK

 

X1111

BANK

X1111

US

US

 

 

X2222

TW

TW

 

 

C3333

CN

 

X2222

CORP

 

Scorecard

 

CustomerId

ScoreCardId

CustomerCreditGrade

ScoreCardDate

ScoreCardApprovedFlag

X1111

32104

AA+

31/12/2016

Y

X1111

32105

AAA

31/3/2017

N

X1111

32103

AA

30/6/2106

Y

X1111

32102

AA-

31/3/2106

Y

X2222

40321

BBB

31/12/2016

N

X2222

40322

BBB+

31/12/2016

Y

 

 

 

You are given the task to write code to generate below output.

 

CustomerId

ScoreCardId

CustomerCreditGrade

DomicileCountryCode

IncorporationCountryCode

CustomerType

A1111

32104

AA+

SG

US

BANK

B2222

32104

AA+

HK

US

BANK

X1111

32104

AA+

US

US

BANK

X2222

40322

BBB+

TW

TW

CORP

C3333

40322

BBB+

CN

TW

CORP

 

Hints:

  • Scorecards are at parent customer Id level and they are assessed on quarterly basis.

  • Select the most recent scorecard, which is approved.

  • Enrich the data that are at parent level to all child customers.

PROC Star
Posts: 1,209

Re: Two Table Join Exercises

[ Edited ]

What code have you tried so far?

New Contributor
Posts: 3

Re: Two Table Join Exercises

i need this in sql....joins

Valued Guide
Posts: 515

Re: Two Table Join Exercises

Please post the code you have written. Repeating what you want won't motivate anybody to do your job.

Super User
Posts: 13,286

Re: Two Table Join Exercises

You may get more help by posting all example data in the form of a data step. If you don't know how to convert your SAS data into data step code Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

One reason the data step is good idea is then we do not have to guess which of your variables may be numeric or character and whether any date variables are actually SAS date values or not (there are a large number of things that you can do with SAS date valued variables that are impractical directly with other forms of dates).

 

As a minimum you will have to explain such things as how "amount" in your example output is generated from the amounts in the input data.

 

Also how does the MonthEndDate apply to the process. For instance you have a value of MonthEndDate of 30/3/2017 for AccountId 111 CountryCode SG ProductCode LON for the transactiondate of 20/10/2016. Since your example output only shows 31/3/2017 for the MonthEndDate does that mean this particular record is excluded? Or is there some additional combination rule you haven't describe.

Ask a Question
Discussion stats
  • 6 replies
  • 200 views
  • 2 likes
  • 5 in conversation