The SAS Output Delivery System and reporting techniques

A crosstab report with no data summary or aggregation

Accepted Solution Solved
Reply
Contributor
Posts: 54
Accepted Solution

A crosstab report with no data summary or aggregation

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.


Accepted Solutions
Solution
‎01-20-2012 09:56 AM
SAS Super FREQ
Posts: 8,740

Re: A crosstab report with no data summary or aggregation

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;

View solution in original post


All Replies
Super User
Posts: 9,671

Re: A crosstab report with no data summary or aggregation

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

Contributor
Posts: 54

A crosstab report with no data summary or aggregation

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

Solution
‎01-20-2012 09:56 AM
SAS Super FREQ
Posts: 8,740

Re: A crosstab report with no data summary or aggregation

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;

Super User
Posts: 9,671

Re: A crosstab report with no data summary or aggregation

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

SAS Super FREQ
Posts: 8,740

A crosstab report with no data summary or aggregation

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

Contributor
Posts: 54

A crosstab report with no data summary or aggregation

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:

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 440 views
  • 4 likes
  • 3 in conversation