Desktop productivity for business analysts and programmers

Merging Two data sets.

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Merging Two data sets.

Hi Techies,

 

I had two files :

 

Product file with variables

COUNTRY |REP PERIOD|PRODUCT|CUS BAL|TOTAL Internal REVENUE|TOTAL Company REVENUE

Mhh|201609|BONDS|190287090.36|6403.43|35133.79
Mhh|201609|CARDS|671324094.71|173433.63|7456720.33
Mhh|201609|INSURANCE DISTRIBUTION|319084278.75|1200.88|32716.46
Mhh|201609|LIABILITIES|8221493003.19|1023665.86|6227124.53
Mhh|201609|MORTGAGE|-4966142516.00|866733.69|6418349.77
Mhh|201609|MUTUAL FUNDS|953758774.35|117141.71|995857.09
Mhh|201609|OTHER LOANS|-208178265.09|11068.84|2471757.78
Mhh|201609|STRUCTURED PRODUCTS|1075804405.98|25508.59|486816.84

 

Segment file with variables:

COUNTRY |REP PERIOD|Segment|Client|TOTAL Internal REVENUE|TOTAL Company REVENUE

Mhh|201609|ADV|7874|252777.11|7017855.48
Mhh|201609|PERSONAL|12965|398750.38|8943615.55
Mhh|201609|PRE|26376|1573629.14|8163005.54

 

 

Now i neeed to create single dataset using those two different files in which the summary of the TOTAL Internal REVENUE|TOTAL Company REVENUE should be equal for each file.

 

I mean if we add all the revenues for three segemnts that value should match for the products revenue for each month.

 

Can any one please provide me the code to create my final dataset.

 

Thanks.

 

 

 


Accepted Solutions
Solution
‎12-06-2016 11:09 AM
Trusted Advisor
Posts: 1,475

Re: Merging Two data sets.

I think I got your point.

Attached is a program that convert data to SAS datasets and sum them per contry  rep_month

just to verify that totals equal on both inputs.

 

I can help in sas programing but I can't design a solution how to parse products among segments.

View solution in original post

Attachment

All Replies
Trusted Advisor
Posts: 1,475

Re: Merging Two data sets.

Some clarification need to help you more:

1)  have you tried to import your files into sas datasets ? if positive - what code did you use ?

2)  what is your unit to sum values ?

     Is it: country - period - product ?

     Do you need summary per coutry-priod ? per country ? per period ? per product? all levels ?

 

Please be more specific.

 

Contributor
Posts: 20

Re: Merging Two data sets.

Hi Shmuel,

 

My goal is to create the final dataset ( Single or Two ) for the dashboard.

 

In which the dashboard contains the data granularity as per the country -> Reporting Month -> Segment -> Product.

 

If we filter according to the above granulartity the total revenues should match according to the filter selection..

 

Is this possible on the current data files i.e segment and product.

 

Thanks

 

 

Solution
‎12-06-2016 11:09 AM
Trusted Advisor
Posts: 1,475

Re: Merging Two data sets.

I think I got your point.

Attached is a program that convert data to SAS datasets and sum them per contry  rep_month

just to verify that totals equal on both inputs.

 

I can help in sas programing but I can't design a solution how to parse products among segments.

Attachment
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 244 views
  • 0 likes
  • 2 in conversation