Combining Two datasets with a single data statement

Solved
Occasional Contributor
Posts: 10

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!

Accepted Solutions
Solution
‎02-08-2017 11:05 AM
Super Contributor
Posts: 474

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

All Replies
Super User
Posts: 9,427

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.

Occasional Contributor
Posts: 10

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!

Solution
‎02-08-2017 11:05 AM
Super Contributor
Posts: 474

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

Occasional Contributor
Posts: 10