BookmarkSubscribeRSS Feed
Vinay-K
Calcite | Level 5

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.

 

6 REPLIES 6
Vinay-K
Calcite | Level 5

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.

PeterClemmensen
Tourmaline | Level 20

What code have you tried so far?

Vinay-K
Calcite | Level 5

i need this in sql....joins

andreas_lds
Jade | Level 19

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

ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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