Combining Two datasets with a single data statement

[Gcruzg]

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!

Thanks in advance! Kind regards! 

[RW9]

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.

[Gcruzg]

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!

[DanielSantos · Accepted solution]

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

[Gcruzg]

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

Dude, U rock!