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!
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
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.
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!
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.