topic Re: Insert Zero Values Into Ordered Table for Cumulative Sum in SAS Programming

Insert Zero Values Into Ordered Table for Cumulative Sum

StephenOverton — Wed, 29 Jul 2009 03:29:49 GMT

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!

Re: Insert Zero Values Into Ordered Table for Cumulative Sum

sbb — Wed, 29 Jul 2009 05:48:43 GMT

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.

Re: Insert Zero Values Into Ordered Table for Cumulative Sum

deleted_user — Wed, 29 Jul 2009 12:48:22 GMT

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;

Re: Insert Zero Values Into Ordered Table for Cumulative Sum

StephenOverton — Wed, 29 Jul 2009 13:08:04 GMT

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