topic Re: Combining Two datasets with a single data statement in SAS Programming

Combining Two datasets with a single data statement

Gcruzg — Wed, 08 Feb 2017 10:15:21 GMT

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!

Re: Combining Two datasets with a single data statement

RW9 — Wed, 08 Feb 2017 10:35:11 GMT

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.

Re: Combining Two datasets with a single data statement

Gcruzg — Wed, 08 Feb 2017 10:43:24 GMT

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!

Re: Combining Two datasets with a single data statement

DanielSantos — Wed, 08 Feb 2017 16:03:10 GMT

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

Re: Combining Two datasets with a single data statement

Gcruzg — Wed, 08 Feb 2017 16:09:08 GMT

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

Dude, U rock!