BookmarkSubscribeRSS Feed
RobW
Calcite | Level 5
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
2 REPLIES 2
RobW
Calcite | Level 5
bah...Cynthia must be teaching today 😉
Cynthia_sas
SAS Super FREQ
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]

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
  • 2 replies
  • 623 views
  • 0 likes
  • 2 in conversation