The SAS Output Delivery System and reporting techniques

How to place the primary (first) group value at the top of the second group

Reply
Contributor
Posts: 22

How to place the primary (first) group value at the top of the second group

Hi..



Real quick background info. I have data that will be grouped on two variables, clerk_id and date. The resulting summary will be columnar tallies of work type completed. My goal here is to report each clerk's output over a given week (therefore clerk_id/date grouping), but instead of having a dedicated 1st column to the clerk_id value, I would rather put the value (say, JONDOE) at the top of the group but in the second (date) column. I am using some hidden columns so far...hiding clerk_id and numeric date (in order to control the ordering of the date) and then another column for date that is character with the weekdate9. format that was created prior to the proc report step.

basically this is roughly what I would want the layout to look like

JOHNDOE
Monday
Tuesday
Wednesday
Thursday
Friday
Weekly Totals

I can get the 'compute after clerk_id' to work, showing 'Weekly Totals' as the value for Date and then use the 'break after clerk_id / summarize' to get the aggregate totals for the week. I can't get the JOHNDOE in at the top using a 'computer before clerk_id' without using a 'break before clerk_id / summarize'. I don't want 'totaled' data at the top of the group, only the bottom. Also, I have trouble getting the clerk_id value into the date column during the 'compute before clerk_id' block.

Thanks in advance

rmw
Contributor
Posts: 22

Re: How to place the primary (first) group value at the top of the second group

bah...Cynthia must be teaching today Smiley Wink
SAS Super FREQ
Posts: 8,744

Re: How to place the primary (first) group value at the top of the second group

Hi:
You can do what you want with PROC REPORT. It takes a bit of adjustment, especially if your date is a numeric variable, which is what I show in the program below.

First, Clerk_id and name are made NOPRINT variables. Date is also a NOPRINT variable. PRTDATE is a computed variable -- it will either hold the character string for the date or it will hold the value "Weekly Totals" at the summary line from the BREAK statement. The format WKDT serves a double purpose -- it will use the weekdate18. format for any date in the file, but then it will also use the label "Weekly Total" when date is missing. Since I set date to missing on the BREAK line (see the IF statement in the COMPUTE block for PRTDATE), I know that it occurs on the break line.

Because Clerk_ID and NAME both occur on the first summary line, I use COMPUTE BEFORE NAME to write the pseudo-header line that contains the CLERK_ID and the NAME for every group of days for that person. You do NOT need to perform a summarize in order to write something out with a LINE statement, when you are doing the COMPUTE BEFORE an order or group variable.

I used HTML because I wanted to left justify the string written with the COMPUTE BEFORE NAME and I wanted to use the HEADER style for the BEFORE LINE and the Weekly Total line.

Hope this helps,
cynthia

ps...not teaching...I was at a user group conference, presenting a paper.

[pre]
data clerk;
infile datalines;
input clerk_id name $ date : mmddyy10. numvar;
return;
datalines;
111 Alan 06/02/2008 10
111 Alan 06/02/2008 10
111 Alan 06/02/2008 10
111 Alan 06/03/2008 10
111 Alan 06/03/2008 10
111 Alan 06/04/2008 10
111 Alan 06/04/2008 10
111 Alan 06/05/2008 10
111 Alan 06/05/2008 10
111 Alan 06/05/2008 10
111 Alan 06/06/2008 10
111 Alan 06/06/2008 10
112 Bob 06/02/2008 15
112 Bob 06/02/2008 15
112 Bob 06/03/2008 15
112 Bob 06/03/2008 15
112 Bob 06/03/2008 15
112 Bob 06/04/2008 15
112 Bob 06/04/2008 15
112 Bob 06/05/2008 15
112 Bob 06/05/2008 15
112 Bob 06/05/2008 15
112 Bob 06/06/2008 15
112 Bob 06/06/2008 15
;
run;

proc format;
value wkdt low-high= [weekdate18.]
. = 'Weekly Totals';
run;

ods html file='c:\temp\Clerk.html' style=sasweb;
proc report data=clerk nowd
style(summary)=Header;
column clerk_id name date prtdate numvar;
define clerk_id / group noprint;
define name / group noprint;
define date / group order=internal noprint;
define prtdate / computed;
define numvar / sum;
break after clerk_id / summarize skip;
compute before name /
style=Header{just=left};
line 'Clerk Info: ' clerk_id 3.0 ' ' name $8.;
endcomp;
compute prtdate/ character length=30;
if upcase(_break_) = 'CLERK_ID' then
date = .;
prtdate = put(date,wkdt.);
endcomp;
compute after clerk_id;
line ' ';
endcomp;
run;
ods html close;
[/pre]
Ask a Question
Discussion stats
  • 2 replies
  • 125 views
  • 0 likes
  • 2 in conversation