Merging Two data sets.

[vula]

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.

[Shmuel]

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.

[vula]

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

[Shmuel · Accepted solution]

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.