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.
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.
You're supposed to do your homework yourself. Post what you already tried, and where you run into problems.
What code have you tried so far?
i need this in sql....joins
Please post the code you have written. Repeating what you want won't motivate anybody to do your job.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.