DATA Step, Macro, Functions and more

Combining Two datasets with a single data statement

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

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!

 

Thanks in advance! Kind regards! 


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

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

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

Re: Combining Two datasets with a single data statement

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

Dude, U rock!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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