Fluorite | Level 6

## Combining Two datasets with a single data statement

Hello all!

I have a bit problem, so here it goes.

I have a data set with the following records:

Fond         Money   Cumulative_Money

Fond_1     \$500     \$500

Fond_2     \$300     \$800

Fond_3     \$1000   \$1800

Then, I have another dataset with the following information:

Dept        Money_Dpt.

D_1         600

D_2         700

So, I would need a loop, or a do-until sentence, in order to obtain the next result:

Fond          Money    Percentage_Money_Dpt                  Dpt.

Fond_1      \$500       \$500 <= \$600, then 100%               D_1

Fond_2      \$300       \$300 <= (\$600-\$500), then I divide the record into two new records:

Fond_2A    \$100       \$100 <= (\$600-\$500) then 100%    D_1

Fond_2B    \$200       \$200 <= \$700      then 100%           D_2

I would appreciate a lot your help! U rock!

1 ACCEPTED SOLUTION

Accepted Solutions
Barite | Level 11

## Re: Combining Two datasets with a single data statement

Hi.

Not totally sure of how this should working, as there seem to be some missing pieces...

For example, total Money_Dpt sums 1300, and you have 1800 in table FOND to distribute...

Anyway, here's a try that will load the DPT table into an hash table (in memory) and work from there to distribute the amounts of in the FOND table.

data WANT;
set FOND;
keep FOND Money Cumulative_Money Dept; * keep result vars; if 0 then set DEPS; * merge table layouts; if _N_ eq 1 then do; declare hash HD(dataset: 'DPT'); _RC = HD.defineKey('Dept'); _RC = HD.defineData(all:'yes'); HD.defineDone(); * hash to load Depts; declare hiter HI('HD'); * iterator to traverse it; end; _Money=0; _RC=HI.first(); * get first Dept in iterator; do until (HI.next()); if Money gt Money_Dpt then do; * check if enough; _Money=Money-Money_Dpt; Money=Money_Dpt; end; else _Money=0; * enough, set 100%; if Money gt 0 then output; * if done, output; Money_Dpt+(-Money); * adjust amount available for Dept; Money=_Money; * adjust to remaining money to fund; _RC = HD.replace(); * replace amount available for Dept;
end; run;

This will get you the following for the example you provided:

FOND    Money  Money  Dept

Fond_1  500    500    D_1
Fond_2  100    800    D_1
Fond_2  200    800    D_2
Fond_3  500    1800   D_2

Of course, Fond_3 gets only 500 of the (1000 needed) from D_2, since there is no more Money there.

Hope it helps.

Daniel Santos @ www.cgd.pt

4 REPLIES 4
Diamond | Level 26

## Re: Combining Two datasets with a single data statement

Post test data in the form of a datastep.  Your example doesn't make sense either.  Why is number two split,

\$300 <= (\$600-\$500), then I divide the record into two new records:

But in your given test data neither 600 or 500 are present in that row of data, I suppose 600 could come from teh second example, but still don't see any 500 at all for that observation.

Fluorite | Level 6

## Re: Combining Two datasets with a single data statement

You're right!

\$600 comes from the second dataset, and \$500 is the first money amount in the first data set.

I explain to you. I need to divide the second record into two because \$300 is not less or greater than \$600 that I recieved from Department_1 minus \$500 I spent. So, I divide into: a first record call Fond_2A where I say: the first \$100 I have spent are from the first department, and the rest \$200 I use the money from Department_2.

Kind regards!

Barite | Level 11

## Re: Combining Two datasets with a single data statement

Hi.

Not totally sure of how this should working, as there seem to be some missing pieces...

For example, total Money_Dpt sums 1300, and you have 1800 in table FOND to distribute...

Anyway, here's a try that will load the DPT table into an hash table (in memory) and work from there to distribute the amounts of in the FOND table.

data WANT;
set FOND;
keep FOND Money Cumulative_Money Dept; * keep result vars; if 0 then set DEPS; * merge table layouts; if _N_ eq 1 then do; declare hash HD(dataset: 'DPT'); _RC = HD.defineKey('Dept'); _RC = HD.defineData(all:'yes'); HD.defineDone(); * hash to load Depts; declare hiter HI('HD'); * iterator to traverse it; end; _Money=0; _RC=HI.first(); * get first Dept in iterator; do until (HI.next()); if Money gt Money_Dpt then do; * check if enough; _Money=Money-Money_Dpt; Money=Money_Dpt; end; else _Money=0; * enough, set 100%; if Money gt 0 then output; * if done, output; Money_Dpt+(-Money); * adjust amount available for Dept; Money=_Money; * adjust to remaining money to fund; _RC = HD.replace(); * replace amount available for Dept;
end; run;

This will get you the following for the example you provided:

FOND    Money  Money  Dept

Fond_1  500    500    D_1
Fond_2  100    800    D_1
Fond_2  200    800    D_2
Fond_3  500    1800   D_2

Of course, Fond_3 gets only 500 of the (1000 needed) from D_2, since there is no more Money there.

Hope it helps.

Daniel Santos @ www.cgd.pt

Fluorite | Level 6

## Re: Combining Two datasets with a single data statement

Thank you so much Daniel! It's exactly what I need, apart from some changes I will make...

Dude, U rock!
Discussion stats
• 4 replies
• 955 views
• 2 likes
• 3 in conversation