Dear SAS Heads- I am working on a program to calculate the replacement costs and ongoing annual maintenance costs of roads over a ten year period. I have a very large dataset with many roads, so this example is an over-simplification in order to get the code correctly specified. I am having difficulty with a complicated data step, so I am looking for the "best-of-the-best" BASE SAS programmers. I know you are out there. Background: My example raw dataset, "have", contains two roads (road_id=1 and road_id=2). The dataset contains the age of the road in 2012 ("Age_YE2012"), the current age in a particular year ("age"), useful lifespan ("Lifespan"), the year ("Year_Ending"), whether or not the road was replaced ("Replace"), the one-time cost to replace the road ("CAPEX"), and the maintenance cost of the road when it is 1 years old ("OPEX_Age1"). Here is my example raw dataset: ******; DATA have; INPUT Road_id Age_YE2012 Lifespan Year_Ending Age Replace $ CAPEX OPEX_Age1; DATALINES; 1 5 2013 7 6 NO 1000000 1000 1 5 2014 7 7 NO 1000000 1000 1 5 2015 7 8 NO 1000000 1000 1 5 2016 7 9 NO 1000000 1000 1 5 2017 7 10 NO 1000000 1000 1 5 2018 7 11 NO 1000000 1000 1 5 2019 7 12 NO 1000000 1000 1 5 2020 7 13 NO 1000000 1000 1 5 2021 7 14 NO 1000000 1000 1 5 2022 7 15 NO 1000000 1000 1 5 2023 7 16 NO 1000000 1000 2 6 2013 8 7 NO 2000000 3000 2 6 2014 8 8 NO 2000000 3000 2 6 2015 8 9 NO 2000000 3000 2 6 2016 8 10 NO 2000000 3000 2 6 2017 8 11 NO 2000000 3000 2 6 2018 8 12 NO 2000000 3000 2 6 2019 8 13 NO 2000000 3000 2 6 2020 8 14 NO 2000000 3000 2 6 2021 8 15 NO 2000000 3000 2 6 2022 8 16 NO 2000000 3000 2 6 2023 8 17 NO 2000000 3000 ; run; ********; Question: I need a datastep or a couple of datasteps to perform the following tasks: (1) Use a combination of the current "Age" and "Lifespan" of the road to determine the year(s) when the road will need to be replaced over that 10 year period. Basically, if the age exceeds the lifespan, then in the first year when that happens I need the following values to change: REPLACE="YES" (this indicates the road was replaced that particular year) CAPEX_ReplaceYr=CAPEX (this indicates that the cost of replacement occurs during the year of that replacement) OPEX=OPEX_Age1 (this indicates that this specific maintenance cost occurs during the first year of replacement) Note that in some cases, the road may need to be replaced twice in that ten year period. This happens in my example for Road_ID=1. (2) I will need SAS to also adjust the "Age" variable if the road was replaced in Step #1. For example, Road_ID=1 will need to be replaced in 2015 and 2022. So, the "Age" in 2015 is 1, 2016 is 2 years old, 2017 is 3 years old and so on until the road is replaced again (2022) and the age gets reset back to 1 and the counting begins again. (3) Finally, I need SAS to escalate/increase the "OPEX" value by 10% above the starting value for Age 1 based on the actual "Age" of the road. For example, in 2015, Road_id=1 is only 1 years old, so its OPEX=1000 (i.e., OPEX_Age1). In 2016, this same road is now 2 years old, so the OPEX increases by 10% to 1100. If this road is six years old (in years 2013 and 2020) then the OPEX value is 1500. You can see how the final dataset should look below in my example dataset called "want". Anyway, I'm hopeful that someone out there can see this solution very quickly. I do not think this is going to be a large data step--probably some proc SQL and/or do loops. I should mention that I DO NOT have IML installed on my machine. Anyway, I look forward to hearing from you. Thank you so much. Please let me know if you have any additional questions. -Pete *******; DATA want; INPUT Road_id Age_YE2012 Lifespan Year_Ending Age Replace $ CAPEX OPEX_Age1 CAPEX_ReplaceYr OPEX; DATALINES; 1 5 2013 7 6 NO 1000000 1000 . 1500 1 5 2014 7 7 NO 1000000 1000 . 1600 1 5 2015 7 1 YES 1000000 1000 1000000 1000 1 5 2016 7 2 NO 1000000 1000 . 1100 1 5 2017 7 3 NO 1000000 1000 . 1200 1 5 2018 7 4 NO 1000000 1000 . 1300 1 5 2019 7 5 NO 1000000 1000 . 1400 1 5 2020 7 6 NO 1000000 1000 . 1500 1 5 2021 7 7 NO 1000000 1000 . 1600 1 5 2022 7 1 YES 1000000 1000 1000000 1000 1 5 2023 7 2 NO 1000000 1000 . 1100 2 6 2013 8 7 NO 2000000 3000 . 4800 2 6 2014 8 8 NO 2000000 3000 . 5100 2 6 2015 8 1 YES 2000000 3000 2000000 3000 2 6 2016 8 2 NO 2000000 3000 . 3300 2 6 2017 8 3 NO 2000000 3000 . 3600 2 6 2018 8 4 NO 2000000 3000 . 3900 2 6 2019 8 5 NO 2000000 3000 . 4200 2 6 2020 8 6 NO 2000000 3000 . 4500 2 6 2021 8 7 NO 2000000 3000 . 4800 2 6 2022 8 8 NO 2000000 3000 . 5100 2 6 2023 8 1 YES 2000000 3000 2000000 3000 ; run;
... View more
