BookmarkSubscribeRSS Feed
StephenOverton
Fluorite | Level 6
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!
3 REPLIES 3
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Not applicable
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
Fluorite | Level 6
Actually this is EXACTLY what I was looking for. Thanks a bunch!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 754 views
  • 0 likes
  • 3 in conversation