DATA Step, Macro, Functions and more

Accounting for missing dates

Reply
N/A
Posts: 0

Accounting for missing dates

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?
SAS Super FREQ
Posts: 8,868

Re: Accounting for missing dates

Posted in reply to deleted_user
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
SAS Employee
Posts: 105

Re: Accounting for missing dates

Posted in reply to deleted_user
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;
Super Contributor
Super Contributor
Posts: 3,174

Re: Accounting for missing dates

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.
Ask a Question
Discussion stats
  • 3 replies
  • 133 views
  • 0 likes
  • 4 in conversation