I've been trying to explore different ways to insert values of zero into a table where there is a missing year/month. The table is ordered by a location ID, year, and month,
Example:
location_id year month total
273 2009 1 11
273 2009 2 30
273 2009 4 5
273 2009 5 27
So in this example I would need to insert a new row of: 273 2009 3 0
I need to loop through every row in this (much larger) table and insert a value of zero for 'total' for each missing year and month for every location_id. I can always re-sort the table so appending at the end is acceptable. This seems like a simple loop with some variable checking. I'm not the best at SAS syntax but I do understand programming logic.
From here I plan on finding the cumulative sum. This is the goal I need to achieve, simply finding the cumulative sum per location_id per year/month. And obviously having a missing month will result in a missing cumulative sum, which is the purpose for inserting rows with total=0.
Any help is much appreciated!