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;
... View more