Hello, I'm trying to find a price value associated with upgrading arcade machines. My datasets contain store inventory of arcade machines and their associated price. I'm trying to find each years increase in cost associated with UPGRADING the machines. sample dataset, I have a dataset YEARLYSTATEMENT_yyyy from 2015-2020, there are around about 400-600 rows each year. YEARLYSTATEMENT_2015 STORE_NUM MACHINE_INV PRICE 005 P006 301 001 P001 212 004 P016 351 012 P091 462 007 P092 504 005 P093 520 005 P090 480 012 P014 322 003 P015 412 etc.. ... ... Each store keeps an inventory of each machine that they currently have and it reflects that here under machine_inv Upgrading the machine is not simply buying a new one but buying a new model of the same kind of machine, I also have a small list of upgrades by the manufacturer that looks like this: P014->P015->P016 P090->P091->P092->P093 P055->P056->P057 P080->P081->P082 Every year an arcade will buy new machines and upgrade old ones, i'm trying to find the cost associated with only upgrading to new machines year to year. For example in 2015 if 100,000 was our inventory worth on machines for all stores, and in 2016 130,000 was the new value, I want to see how much of that increase of 30,000 was solely because we upgraded the machines and not purchased a separate model. Another factor that complicated this is that an upgrade can be from P014 -> P015 or it can also be P014 -> P016, any increase in the upgrade "chain" will count as an upgrade. When an arcade upgrades a machine, they will no longer carry any of the previous version. An idea i had to tackle this was to single out each machine that has an upgrade path and then add the cost up and subtract by the number the previous year received. But by doing this I cant account for if the store had the previous model or not or if they just purchased one of these machines outright. Is there a way in sas that I can easily code to look if a previous year carried a machine in previous part of the upgrade chain and calculate the cost associated with upgrading that year? Sorry if this is kind of a complex problem, I'm just not sure how to tackle this at all. I would really appreciate any help, insights, or ideas.
... View more