topic Re: Accounting for missing dates in SAS Programming

Accounting for missing dates

deleted_user — Sun, 16 Nov 2008 11:03:09 GMT

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?

Re: Accounting for missing dates

Cynthia_sas — Sun, 16 Nov 2008 12:33:16 GMT

Hi:
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"!

cynthia

Re: Accounting for missing dates

yonib — Sun, 16 Nov 2008 14:43:07 GMT

If i get what you want this example may help you:

/*for example: this is your data*/
data test;
LENGTH date $10.;
input Number_of_Sightings date $;
date_new=input(date,ddmmyy10.);
week=week(date_new); /*return the number of week according to this year*/
format date_new ddmmyy10.;
cards;
10 01112008
12 05112008
1 06112008
3 09112008
4 14112008
;
run;

/*count sightings per week*/
proc sql;
select count(Number_of_Sightings) as Sightings
,week
from test
group by week;
quit;

Re: Accounting for missing dates

sbb — Mon, 17 Nov 2008 01:30:28 GMT

You can setup a "all possible week-ending dates" file as a placelholder, using a DATA step, such as:

DATA WKDATES;
FORMAT WKENDDT DATE9.;
DO WKENDDT=INTNX('WEEK',TODAY(),-5,'E') TO INTNX('WEEK',TODAY(),-0,'E') BY 7;
OUTPUT;
END;
STOP;
RUN;

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.

Scott Barry
SBBWorks, Inc.