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.

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