Let's pretend I have a file on UFO sightings with two items: Date and Number_of_Sightings. I must make a report on the number of UFO sightings per week, with the week running Saturday to Friday. I can not simply output a running total when weekday(Date) eq 6 because my input file includes only the dates when there was at least one UFO sighting. No spaceships - no record for that day. Is there some (hopefully elegant) way of handling this?
With several procedures -- most notably for producing reports, Proc Report and Proc Tabulate -- there is a way to "preload" a user-defined format that gives info on the WHOLE universe of situations for which you want to have a report.
It wasn't exactly clear to me what you wanted, but if you wanted to see a day listed, even if it had zero UFO sightings, with a total at the bottom of the whole week, then perhaps this previous forum posting has some code that would be useful: http://support.sas.com/forums/thread.jspa?messageID=9017⌹
You'll also want to pay attention to the ORDER= option so you can get the days of the week sorted in the right order. Perhaps you'll find that there are more UFO sightings on "party nights"!
/*for example: this is your data*/
LENGTH date $10.;
input Number_of_Sightings date $;
week=week(date_new); /*return the number of week according to this year*/
format date_new ddmmyy10.;
/*count sightings per week*/
select count(Number_of_Sightings) as Sightings
group by week;
You can setup a "all possible week-ending dates" file as a placelholder, using a DATA step, such as:
FORMAT WKENDDT DATE9.;
DO WKENDDT=INTNX('WEEK',TODAY(),-5,'E') TO INTNX('WEEK',TODAY(),-0,'E') BY 7;
Then create a week-summary file of your sightings data, join the two files, and generate your desired report. Use the INTNX function to assign a week-ending date to use with grouping your detail data up to a week-summary level.