BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Gcruzg
Fluorite | Level 6

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
DanielSantos
Barite | Level 11

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

View solution in original post

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

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
Barite | Level 11

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
Fluorite | Level 6
Thank you so much Daniel! It's exactly what I need, apart from some changes I will make...

Dude, U rock!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 781 views
  • 2 likes
  • 3 in conversation