How to projecting different values - need help with do loops and or first/last syntax

[PeterEskild]

Hi everybody

This is my first time in this forum. I hope there is one out there who can help me with this question. The final solution is probarly gonna be some kind of mix of do while/until or then and last/first syntax. I'm just

not sure how to do it.

My dateset consist of some 2500 different ID's with values (below is a small amount of that). Each ID has a acquisition price (when type=1) and when type=3 it stand for that the ID's has been depreciated.

Each ID has to be depreciated until the sum of the depreciated values equels the acquisition price. The way the depreciating has been done have changed over time so what I have written below I think is the best way to take account of that.

There are the following to be aware of in data:

- The acquisition price can both be postive (then negative depreciating values) and negative (then postive depreciating values)

- The amount that is depreciated with, is the acquisition price divided with the lifespan (36, 48 or 60 months) (but it's not always the case. values can be transferred back and the first record of depreciation can be different from the acquisition price/lifespan ).

- You can't assume that lifespan*depreciated value will give the acquisition price. Values can be put back in and there is almost every time a little amount left in the last record per ID (like 0,05) so solution has

  to take account for that.

1) So the solution which I think would be best is that all those ID's which already have been depreciated at least once, will be depreciated with acquisition price divided with the lifespan. So new records in the dataset have to be made per ID until the sum is zero as mentioned. Note that the solution has to take account that last record per ID can be 0.03 or 1.43 to get the sum of all the depreciated values + acquisition price to be zero or cancel out (until cumprofit in date below is 0).

2) Then efter this those ID's which haven't been depreciated yet (those ID's with only type=1) have to be depreciated. The first depreciation value can be different than off acquisition price/lifespan.

There are two dates in the dateset below. If the ID have a "last_date" that it's different from 1st in the month, then the first depreciated value will be different from acquisition price/lifespan.

- I have calculated those in depreciate_1 but it's only for new ID's is should be used.

- depreciate_2 is if trans_date and last_date isn't in the same month. Then first depreciated value will be month_#*(amount/lifetime)+depreciate_1.

This rule only goes for new ID's because it doesn't always apply to ID's which are older. That's why I wanted this part (2) separeted in a datestep from the task above (1). I hope that can be done.

I have mentioned it in the coding below and when running it in SAS it's fast to see when opening the last dataset "projecting4". I have attached the code in a SAS file as well.

I truly hope somebody can help me. Many thanks in advance.

CODE:

data projecting;

input ID $ 1-2 type amount trans_date date9. lifetime last_date date9.;

format trans_date last_date  DDMMYY10. amount commax14.2;

infile cards dlm=">";

cards;

S1>1>284375.00>01feb2014>60>01feb2014

S1>3>-4739.58>28feb2012>60>01feb2014

S1>3>-4739.58>31mar2012>60>01feb2014

S1>3>-4739.58>30apr2012>60>01feb2014

S1>3>-4739.58>31may2012>60>01feb2014

S1>3>-4739.58>30jun2012>60>01feb2014

S1>3>-4739.58>31jul2012>60>01feb2014

S1>3>52135.38>31aug2012>60>01feb2014

S1>3>-4739.58>30sep2012>60>01feb2014

S1>3>-4739.58>31oct2012>60>01feb2014

S1>3>-4739.58>30nov2012>60>01feb2014

T2>1>31062.5>21feb2014>60>21feb2014

T2>3>-147.92>28feb2014>60>21feb2014

T2>3>-517.71>28feb2014>60>21feb2014

T2>3>-517.71>28feb2014>60>21feb2014

T2>3>-517.71>28feb2014>60>21feb2014

R3>1>178461.25>01mar2014>60>28feb2014

R3>3>-3080.58>31mar2014>60>28feb2014

R3>3>2974.35>30apr2014>60>28feb2014

R3>3>2974.35>31may2014>60>28feb2014

R3>3>2974.35>30jun2014>60>28feb2014

G4>1>414.00>23jun2014>60>23jun2014

G4>3>-1.84>30jun2014>60>23jun2014

G4>3>-6.90>31jul2014>60>23jun2014

G4>3>-6.90>31aug2014>60>23jun2014

U5>1>20371.88>30jun2014>60>30jun2014

U5>3>-11.32>30jun2014>60>30jun2014

U5>3>339.53>31jul2014>60>30jun2014

U5>3>339.53>31aug2014>60>30jun2014

P6>1>10500.00>30dec2013>36>01jan2014

P6>3>-291.66>30jan2014>36>30jan2014

P6>3>-291.66>28feb2015>36>28feb2015

P6>3>-291.66>30mar2016>36>30mar2016

P6>3>-291.66>30apr2017>36>30apr2017

L7>1>2000.00>30dec2013>36>02jan2014

M1>1>3000.00>30dec2013>36>30dec2013

N1>1>15000.00>15jan2014>48>15jan2014

W1>1>-41251.47>31dec2012>36>01jun2012

W1>3>8020.90>31dec2012>36>01jun2012

W1>3>1145.96>31jan2013>36>01jun2012

J1>1>-34980.00>31dec2012>36>01apr2009

J1>3>26235.00>31dec2012>36>01apr2009

J1>3>971.67>31jan2014>36>01apr2009

;

run;

data projecting2;

set projecting;

if type=1 then do;

*First day in month;

firstday =intnx('month',last_date,0,'B');

*Last day in month;

lastDay =intnx('month',last_date,0,'E');

*# days from last_day to last day in month;

depreciate_days =datdif(last_date,lastDay,'act/act')+1;

*# of total days in month;

depreciate_month =datdif(firstday,lastDay,'act/act')+1;

*Only for new ID's.(those ID's with type=1 and no records with type=3.

It works for some of the ID's but since the way of depreciating has changed you can not rely on it 100 percent.;

depreciate_1 =(depreciate_days/depreciate_month)*(amount/lifetime);

*# of months between trans_date and last_date;

month_num =INTCK('MONTH',last_date,trans_date);

*If there is a change in month from transdate to last_date;

depreciate_2 =month_num*(amount/lifetime)+depreciate_1;

format firstday lastDay DDMMYY10. depreciate_1 depreciate_2 commax14.2;

end;

run;

proc sort data=projecting2;by id type trans_date;run;

data projecting3;

set projecting2;

by id;

  profit=sum(amount);

retain cumprofit;

if first.id then cumprofit=0;

cumprofit=sum(cumprofit,profit);

format cumprofit profit amount commax10.2;

run;

proc sort data=projecting3;by id type trans_date;run;

data projecting4;

retain id type profit cumprofit trans_date last_date lifetime

  firstday lastDay depreciate_days depreciate_month depreciate_1 depreciate_2;

drop amount firstday lastday;

set projecting3;

run;

[ballardw]

You might want to look at the SAS depreciation functions. They might make your task a little easier as they are designed to do common depreciation tasks. Functions are DACCDB, DACCDBSL, DACCSL, DACCSYD, DACCTAB, DEPDB, DEPDBSL, DEPSL, DEPSYD and DEPTAB. There is also the FINANCE function. If you can match your rules to these functions they are likely to save many headaches.

[PeterEskild]

Hi ballardw. Thanks for the reply. I have just looked and tried some of the different functions and I don't think it will help given the rules of the forecasting of the deprecations values. I would have been a quick fix but i can't see how. I must go to the thinking box again. i hope there are others who might can help with a solution. Again thank you Ballardw for the help.  

[Patrick]

There are quite a few questions in this questions and it's a mixture between "how to solve the business problem" and then how to implement the solution technically. Try to simplify your question and especially try to solve the business problem first and then ask targeted technical questions.

But to give it a start:

- Is the first data set "Projecting" your starting point? Are these the data you're having?

- There are already some depreciation records in "Projecting": Are these the "givens" and we should now calculate the remaining records until fully depreciated?

- How should your "want" data set look like? A record per month?

- For ID "S1": Why is the trans_date for type1 later than the depreciation dates?

- For ID "R3": type1 is a positive value but it looks like the depreciation values are as well positive - why? (same for ID "W1" the other way round). So what do we need to do? Just add this to the remaining value and use it as the basis for the following months acquisition records to be calculated? (assuming that for month ends where we have a transaction we won't calculate anything).

What would help:

- Give us a data set "want"

- Give us a data set "have"

- Explain as concise and clear as possible the transformation rules which lead from "have" to "want"

[PeterEskild]

Hi Patrick. I can see that some of the data I have typed in wrong. I have corrected that in the new SAS file which I have attached.

To have anonomyes data I have changed the names of the ID's and the variable names. The different ID's amounts are taken from the original data except ID B9 which I have made so you can see how every ID should be depreciated to zero. And some of the ID's in the original dataset have been depreciated more, but that would have been a lot of datalines to make and it shouldn't affect anything if I had taken more.

Yes almost every ID has already been depreciated so you can take those as given and that's why I have to find a way to calculate the remaining ones.

There should be a record pr month of each depreciation until fully depreciated. The new records only need to contain the ID name, the amount that the ID is being depreciated of, the cummulated amount and transdate where each new record is +1 month (last day in the month).

I had forgot some of the plus and minus signs but now all the ID's should follow the rule mentioned earlier:

- The acquisition price can both be postive (then negative depreciating values) and negative (then postive depreciating values) - (expect if a deprecation value has been reversed (see S1)

Because the way of depreciations have changed over time the (2) solution should only apply to new ID's. It will work for some of the elderly but not all. If there was a way for the ID's which already have been depreciated once, just to make a new record per ID until the sum of the depreciations values cancel out the acquisition price where the depreciating value would be acquisition price/timeplan. As you can see in the data at S1 sometimes there is a deprecation value which have been reversed (52.135,38). That can happen and that makes it harder I think to program your way out of it.

[Patrick]

O.K. - From what I understand what needs to be done is take whatever is in the data and then "fill the gaps" with even depreciation values so that we reach zero after the lifetime; with a "partial" value if it's not a full month. That should be doable.

What I don't understand: For ID "B9" you have a lifetime of "6" but you're depreciating over "7" months. Is this a typo or how it should be?

[PeterEskild]

Hi again Patrick. B9 is correct. It would have been 6 depreciation values if the trans date was the 1st in the month. Since it isn't there has to be an amount in the last depreciation that's < than acquisition price / lifespan to make it all cancel out. Also like the S1 there can be an amount transferred back. I very much appreciate your help. I hope it can be solved.

[PeterEskild]

Hi everybody again. I have spent some time and come half way with a solution that does some of the things I want it to do. But I need some help with 3 things I hope someone can help me with. I have attached the SAS file and copied it below. I really hope someone can help me with the last couple of things. Thank you a lot in advance.

data projecting;

input ID $ 1-2 type amount trans_date date9. lifetime last_date date9.;

format trans_date last_date  DDMMYY10. amount commax14.2;

infile cards dlm=">";

cards;

S1>1>284375.00>01feb2012>60>01feb2012

S1>3>-4739.58>28feb2012>60>01feb2012

S1>3>-4739.58>31mar2012>60>01feb2012

S1>3>-4739.58>30apr2012>60>01feb2012

S1>3>-4739.58>31may2012>60>01feb2012

S1>3>-4739.58>30jun2012>60>01feb2012

S1>3>-4739.58>31jul2012>60>01feb2012

S1>3>52135.38>31aug2012>60>01feb2012

S1>3>-4739.58>30sep2012>60>01feb2012

S1>3>-4739.58>31oct2012>60>01feb2012

S1>3>-4739.58>30nov2012>60>01feb2012

T2>1>31062.5>21feb2014>60>21feb2014

T2>3>-147.92>28feb2014>60>21feb2014

T2>3>-517.71>28feb2014>60>21feb2014

T2>3>-517.71>28feb2014>60>21feb2014

T2>3>-517.71>28feb2014>60>21feb2014

R3>1>178461.25>01mar2014>60>28feb2014

R3>3>-3080.58>31mar2014>60>28feb2014

R3>3>-2974.35>30apr2014>60>28feb2014

R3>3>-2974.35>31may2014>60>28feb2014

R3>3>-2974.35>30jun2014>60>28feb2014

G4>1>414.00>23jun2014>60>23jun2014

G4>3>-1.84>30jun2014>60>23jun2014

G4>3>-6.90>31jul2014>60>23jun2014

G4>3>-6.90>31aug2014>60>23jun2014

U5>1>20371.88>30jun2014>60>30jun2014

U5>3>-11.32>30jun2014>60>30jun2014

U5>3>-339.53>31jul2014>60>30jun2014

U5>3>-339.53>31aug2014>60>30jun2014

P6>1>10500.00>30dec2013>36>01jan2014

P6>3>-291.66>30jan2014>36>30jan2014

P6>3>-291.66>28feb2014>36>28feb2014

P6>3>-291.66>30mar2014>36>30mar2014

P6>3>-291.66>30apr2014>36>30apr2014

L7>1>2000.00>30dec2013>36>02jan2014

M1>1>3000.00>30dec2013>36>30dec2013

N1>1>15000.00>15jan2014>48>15jan2014

W1>1>-41251.47>31dec2012>36>01jun2012

W1>3>8020.90>31dec2012>36>01jun2012

W1>3>1145.96>31jan2013>36>01jun2012

J1>1>-34980.00>31dec2012>36>01apr2009

J1>3>26235.00>31dec2012>36>01apr2009

J1>3>971.67>31jan2014>36>01apr2009

B9>1>7000>27jan2014>6>27jan2014

B9>3>-188.17>31jan2014>6>27jan2014

B9>3>-1166.67>28feb2014>6>27jan2014

B9>3>-1166.67>31mar2014>6>27jan2014

B9>3>-1166.67>30apr2014>6>27jan2014

B9>3>-1166.67>31may2014>6>27jan2014

B9>3>-1166.67>30jun2014>6>27jan2014

B9>3>-978.48>31jul2014>6>27jan2014

;

run;

data projecting2;

set projecting;

if type=1 then do;

*First day in month;

firstday =intnx('month',last_date,0,'B');

*Last day in month;

lastDay =intnx('month',last_date,0,'E');

*# days from last_day to last day in month;

depreciate_days =datdif(last_date,lastDay,'act/act')+1;

*# of total days in month;

depreciate_month =datdif(firstday,lastDay,'act/act')+1;

*Only for new ID's.(those ID's with type=1 and no records with type=3.

It works for some of the ID's but since the way of depreciating has changed you can not rely on it 100 percent.;

depreciate_1 =(depreciate_days/depreciate_month)*(amount/lifetime);

*# of months between trans_date and last_date;

month_num =INTCK('MONTH',last_date,trans_date);

*If there is a change in month from transdate to last_date;

depreciate_2 =month_num*(amount/lifetime)+depreciate_1;

format firstday lastDay DDMMYY10. depreciate_1 depreciate_2 commax14.2;

end;

run;

proc sort data=projecting2;by id type trans_date;run;

data projecting3;

set projecting2;

by id;

  profit=sum(amount);

retain cumprofit;

if first.id then cumprofit=0;

cumprofit=sum(cumprofit,profit);

format cumprofit profit amount commax10.2;

run;

proc sort data=projecting3;by id type trans_date;run;

data projecting4;

retain id type profit cumprofit trans_date last_date lifetime

  firstday lastDay depreciate_days depreciate_month depreciate_1 depreciate_2;

drop amount firstday lastday;

set projecting3;

run;

*Projecting;

*3 things I need help with to code below:

- For example B9 there should be one more record where B9 it depreciated with 978.48. The same with the other ID's.

- Those new ID's which only have one record where type=1 aren't projected. ID number L7, N1 and M1.

- Those ID's which are rare but where the type=1 value are negative aren't projected either. Here the depreciation values should be positive. ID number J1 and W1.;

data projecting5;

length id $4.;

set projecting4;

by id;

if first.id then do;

          NEW='EXISTING';

          i=0;

          if type=1 then do;

                      rest=profit;

                      dep=profit/lifetime;

                      output;

           end;

           else do;

                      put "ERROR: " id=;

                      stop;

           end;

end;

else do;

           if not last.id then do;

                      tdate=trans_date;

                      rest=rest+profit;

                      output;

           end;

           else do while (rest>=0);

                      i=i+1;

                      NEW='NEW';

                      rest=rest+profit;

                      trans_date=intnx('month',tdate,i,'E');

                      if rest>=0 then output;

           end;

end;

format rest  dep commax14.2 tdate ddmmyy10.;

retain i dep rest new tdate;

keep id profit trans_date last_date rest new dep type;

run;

[Patrick]

Hi @PeterEskild

I believe you're not getting a lot of response to your question because it is too broad. I would have to invest a considerable amount of time only to fully grasp what you've done so far and to fully follow your thinking before I even would get to the point where I could start with spending time on how to solve your remaining problems.

What I would suggest to you:

Try to reduce complexity and to deconstruct the problem. Then ask targeted detail questions where you provide us with sample data as simple as possible and show us what you need. Try to ask one question only per thread.

Once you've got our answer for the "abstracted problem" translate it back into your real world problem and incorporate the answer into your overall solution.

This deconstruction process will take you some time but I promise you that while trying to narrow down your questions and create easy samples for us you will find have the answers on your own - and for the rest there are a lot of people out there happy to help once it doesn't take that long to get to the "how?".

Thanks

Patrick

[PeterEskild]

Hi Patrick

Thank you for the long reply. I will make a new thread and ask one question at a time and upload a datasat and the code I have questions to. I have come a little further with the code but I still need some help. Again thank you and the others who helped.