topic Re: Two Table Join Exercises in SAS Programming

Two Table Join Exercises

Vinay-K — Fri, 17 Nov 2017 07:17:56 GMT

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.

Two Table Join Exercises, #2

Vinay-K — Fri, 17 Nov 2017 07:18:26 GMT

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.

Re: Two Table Join Exercises

Kurt_Bremser — Fri, 17 Nov 2017 07:18:54 GMT

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

Re: Two Table Join Exercises

PeterClemmensen — Fri, 17 Nov 2017 07:19:37 GMT

What code have you tried so far?

Re: Two Table Join Exercises

Vinay-K — Fri, 17 Nov 2017 07:47:35 GMT

i need this in sql....joins

Re: Two Table Join Exercises

andreas_lds — Fri, 17 Nov 2017 08:18:46 GMT

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

Re: Two Table Join Exercises

ballardw — Fri, 17 Nov 2017 15:16:53 GMT

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-data-AKA-generate/ta-p/258712 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.