I am trying to create a report that will output something like the below excel mockup:
(I don't know why some of the cells boaders are so thick)
|
| Worker |
|
|
Priority | SubPriority | Abby | Bill | Cindy |
A | 1 | Pj1 | Pj3 |
|
| 2 | Pj2 |
|
|
A Total |
| 2 | 1 |
|
B | 1 | Pj7 | Pj5 | Pj4 |
| 2 | Pj8 |
| Pj6 |
| 3 | Pj9 |
|
|
B Total |
| 3 | 1 | 2 |
C | 1 |
|
| Pj10 |
| 2 |
|
| Pj11 |
C Total |
|
|
| 2 |
Grand Total | 5 | 2 | 4 |
Notice that the shaded cells are not aggregates. It’s simply a display of the project name.
The data set is below. Thanks art297 for typing it out.
data have;
input Worker $ ProjectName $ Priority $ SubPriority;
cards;
Abby Pj1 A 1
Abby Pj2 A 2
Bill Pj3 A 1
Cindy Pj4 B 1
Bill Pj5 B 1
Cindy Pj6 B 2
Abby Pj7 B 1
Abby Pj8 B 2
Abby Pj9 B 3
Cindy Pj10 C 1
Cindy Pj11 C 2
;
I have tried to put it together roughly with the following but it wants to count or sum where I want the project name to go and it puts the project name separately.
Proc Report data=have missing;
column priority subPriority
Worker
ProjectName ;
define Worker /across;
define priority /group;
define subPriority /order;
define Projectname /display;
title1 'Test 1';
Run;
Is Proc Report the wrong procedure for this?
Thanks.
Hi:
There is a somewhat simpler method, but it does require some more advanced PROC REPORT coding.
For example, if you move PROJECTNAME under WORKER, your across variable, PROC REPORT, by default doesn't like to put character values under ACROSS variables. But, if you make a fake numeric variable that PROC REPORT thinks it needs to summarize (such as I show with the FAKEVAR variable), then you can get collapsing of the character values to happen. Even with FAKEVAR on the report, since I can use NOPRINT, I can keep PROC REPORT happy by having a numeric variable on the report, but I don't have to display it, so that accomplishes what you want.
Next, in order to get your totals, under each PROJECTNAME column, you have to do some special coding at the BREAK to move the value of N (which is also a NOPRINT item under each WORKER name) into the column for PROJECTNAME at the total.
The referencing _c4_, _c6_, etc, is called an ABSOLUTE column referrence. If you do anything with ACROSS items, you frequently need to use the ABSOLUTE column names. There have been many previous forum postings on this topic.
I am not at a computer where I can take or post a screen shot. But this code produced the desired results for me.
cynthia
data workers;
input Worker $ ProjectName : $20. Priority : $20. SubPriority;
return;
datalines;
Abby Pj1 A 1
Abby Pj2 A 2
Bill Pj3 A 1
Cindy Pj4 B 1
Bill Pj5 B 1
Cindy Pj6 B 2
Abby Pj7 B 1
Abby Pj8 B 2
Abby Pj9 B 3
Cindy Pj10 C 1
Cindy Pj11 C 2
;
run;
ods html file='c:\temp\workers.html' style=sasweb;
Proc Report data=workers nowd missing
style(summary)={background=white};
column priority subPriority
Worker,(n ProjectName) fakevar;
define priority /group;
define subPriority /group;
define Worker /across;
define n / noprint;
define Projectname /display
style(column)={background=cxccccff};
define fakevar / computed noprint;
break after priority / summarize;
rbreak after / summarize;
compute fakevar;
fakevar=1;
endcomp;
compute after priority;
_c4_ = put(_c3_,comma6.);
_c6_ = put(_c5_,comma6.);
_c8_ = put(_c7_,comma6.);
Priority = catx(' ',priority,'Total');
line ' ';
endcomp;
compute after;
_c4_ = put(_c3_,comma6.);
_c6_ = put(_c5_,comma6.);
_c8_ = put(_c7_,comma6.);
priority = 'Grand Total';
endcomp;
title1 'Test 1';
Run;
ods html close;
Hohoooo..
It is really not easy. Give you an example.
data have; input Worker $ ProjectName : $20. Priority : $20. SubPriority; cards; Abby Pj1 A 1 Abby Pj2 A 2 Bill Pj3 A 1 Cindy Pj4 B 1 Bill Pj5 B 1 Cindy Pj6 B 2 Abby Pj7 B 1 Abby Pj8 B 2 Abby Pj9 B 3 Cindy Pj10 C 1 Cindy Pj11 C 2 ; run; proc sort data=have; by worker Priority SubPriority;run; data abby(rename=(ProjectName=abby) ) bill(rename=(ProjectName=bill)) cindy(rename=(ProjectName=cindy)); set have; select(worker); when('Abby') output abby; when('Bill') output bill; when('Cindy') output cindy; end; run; data temp(drop=worker); retain Priority SubPriority abby bill cindy; merge abby bill cindy; by Priority SubPriority; run; data want; set temp end=last; by priority; retain total_abby total_bill total_cindy sub_abby sub_bill sub_cindy; if first.priority then do; sub_abby=0; sub_bill=0; sub_cindy=0; end; if not missing(abby) then do;sub_abby+1;total_abby+1;end; if not missing(bill) then do;sub_bill+1;total_bill+1;end; if not missing(cindy) then do;sub_cindy+1;total_cindy+1;end; output; if last.priority then do; priority=catx(' ',priority,'Total:'); call missing(subpriority); abby=put(sub_abby,best8.); bill=put(sub_bill,best8.); cindy=put(sub_cindy,best8.); output; end; if last then do; priority=catx(' ','Grand','Total:'); call missing(subpriority); abby=put(total_abby,best8.); bill=put(total_bill,best8.); cindy=put(total_cindy,best8.); output; end; run; ods listing close; ods html file='c:\report.html' style=sasweb; option missing=' '; proc report data=want nowd; column Priority SubPriority ('Worker' abby bill cindy); define priority/group; run; ods html close; ods listing;
Ksharp
Wow. That is tough. I'm going to spend some time with this and try to make it flexible so I don't have to hardcode in the workers since my actual data has a lot of workers and they change. I'll post back my progress. Thanks!!
Hi:
There is a somewhat simpler method, but it does require some more advanced PROC REPORT coding.
For example, if you move PROJECTNAME under WORKER, your across variable, PROC REPORT, by default doesn't like to put character values under ACROSS variables. But, if you make a fake numeric variable that PROC REPORT thinks it needs to summarize (such as I show with the FAKEVAR variable), then you can get collapsing of the character values to happen. Even with FAKEVAR on the report, since I can use NOPRINT, I can keep PROC REPORT happy by having a numeric variable on the report, but I don't have to display it, so that accomplishes what you want.
Next, in order to get your totals, under each PROJECTNAME column, you have to do some special coding at the BREAK to move the value of N (which is also a NOPRINT item under each WORKER name) into the column for PROJECTNAME at the total.
The referencing _c4_, _c6_, etc, is called an ABSOLUTE column referrence. If you do anything with ACROSS items, you frequently need to use the ABSOLUTE column names. There have been many previous forum postings on this topic.
I am not at a computer where I can take or post a screen shot. But this code produced the desired results for me.
cynthia
data workers;
input Worker $ ProjectName : $20. Priority : $20. SubPriority;
return;
datalines;
Abby Pj1 A 1
Abby Pj2 A 2
Bill Pj3 A 1
Cindy Pj4 B 1
Bill Pj5 B 1
Cindy Pj6 B 2
Abby Pj7 B 1
Abby Pj8 B 2
Abby Pj9 B 3
Cindy Pj10 C 1
Cindy Pj11 C 2
;
run;
ods html file='c:\temp\workers.html' style=sasweb;
Proc Report data=workers nowd missing
style(summary)={background=white};
column priority subPriority
Worker,(n ProjectName) fakevar;
define priority /group;
define subPriority /group;
define Worker /across;
define n / noprint;
define Projectname /display
style(column)={background=cxccccff};
define fakevar / computed noprint;
break after priority / summarize;
rbreak after / summarize;
compute fakevar;
fakevar=1;
endcomp;
compute after priority;
_c4_ = put(_c3_,comma6.);
_c6_ = put(_c5_,comma6.);
_c8_ = put(_c7_,comma6.);
Priority = catx(' ',priority,'Total');
line ' ';
endcomp;
compute after;
_c4_ = put(_c3_,comma6.);
_c6_ = put(_c5_,comma6.);
_c8_ = put(_c7_,comma6.);
priority = 'Grand Total';
endcomp;
title1 'Test 1';
Run;
ods html close;
But Cynthia. Your code implied a problem, if ProjectName has a null value, you code will aslo count one.
See the following sample data Bill has a null Project, your code will produce a wrong result.
data workers; input Worker $ ProjectName : $20. Priority : $20. SubPriority; return; datalines; Abby Pj1 A 1 Abby Pj2 A 2 Bill Pj3 A 1 Cindy Pj4 B 1 Bill Pj5 B 1 Bill . B 4 Cindy Pj6 B 2 Abby Pj7 B 1 Abby Pj8 B 2 Abby Pj9 B 3 Cindy Pj10 C 1 Cindy Pj11 C 2 ; run; ods html file='c:\temp\workers.html' style=sasweb; Proc Report data=workers nowd missing style(summary)={background=white} out=x; column priority subPriority Worker,(n ProjectName) fakevar; define priority /group; define subPriority /group; define Worker /across; define n / noprint; define Projectname /display ' ' style(column)={background=cxccccff}; define fakevar / computed noprint; break after priority / summarize; rbreak after / summarize; compute fakevar; fakevar=1; endcomp; compute after priority; _c4_ = put(_c3_,comma6.); _c6_ = put(_c5_,comma6.); _c8_ = put(_c7_,comma6.); Priority = catx(' ',priority,'Total'); line ' '; endcomp; compute after; _c4_ = put(_c3_,comma6.); _c6_ = put(_c5_,comma6.); _c8_ = put(_c7_,comma6.); priority = 'Grand Total'; endcomp; title1 'Test 1'; Run; ods html close;
Ksharp
Hi:
The OP did not initially post data with a missing ProjectName. I would consider an obs with a missing ProjectName to be an error in the data that needs to be cleaned up, given the original description of the data. It doesn't make sense to me that an obs for someone has a priority and subpriority but no project. But that is not for me to say. It is for the OP to figure out what his data is. Given data with no missing values for ProjectName, my program does what the OP says he wanted.
cynthia
Luckily, there will always be a Project Name. Both solutions worked. In my current situation, Cynthia's solution scales to my actual data set readily so I'm going to go with that one. Thank you both so much for teaching me how to accomplish this. :smileylove:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.