topic A crosstab report with no data summary or aggregation in ODS and Base Reporting

A crosstab report with no data summary or aggregation

Mishka1 — Thu, 19 Jan 2012 19:46:04 GMT

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.

Re: A crosstab report with no data summary or aggregation

Ksharp — Fri, 20 Jan 2012 11:08:30 GMT

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

A crosstab report with no data summary or aggregation

Mishka1 — Fri, 20 Jan 2012 13:52:57 GMT

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!!

Re: A crosstab report with no data summary or aggregation

Cynthia_sas — Fri, 20 Jan 2012 14:56:48 GMT

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;

Re: A crosstab report with no data summary or aggregation

Ksharp — Sat, 21 Jan 2012 03:56:35 GMT

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

A crosstab report with no data summary or aggregation

Cynthia_sas — Sat, 21 Jan 2012 04:47:29 GMT

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

A crosstab report with no data summary or aggregation

Mishka1 — Sat, 21 Jan 2012 19:45:26 GMT

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: