BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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?
3 REPLIES 3
Cynthia_sas
SAS Super FREQ
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
yonib
SAS Employee
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;
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.

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
  • 656 views
  • 0 likes
  • 4 in conversation