Insert Zero Values Into Ordered Table for Cumulative Sum

[StephenOverton]

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!

[sbb]

Suggest you share info (an example) about your INPUT and desired OUTPUT data file structures and observations, including any existing SAS code you have as well as SAS variable information. This additional info will help achieve a most useful reply from subscribers.

Scott Barry
SBBWorks, Inc.

[deleted_user]

Something like the following may work for you (although I don't have a complete picture of your situation):


*** get all of the ID's crossed by the available years ***;

proc freq data=MAIN noprint;
table location_id*year / out=id_year sparse;
run;

*** generate 12 months for each ID and Year combination ***;

data zeros;
set id_year(keep=location_id year);
do month=1 to 12;
temp_total=0;
output;
end;
run;

*** this is unnecessary if already sorted ***;

proc sort data=MAIN out=sorted;
by location_id year month;
run;

*** fill in the holes ***;

data expanded;
merge zeros sorted; by location_id year month;
total = sum( total, temp_total);
drop temp_total;
run;

[StephenOverton]

Actually this is EXACTLY what I was looking for. Thanks a bunch!