Proc report without hardcoding variables

[Q1983]

data have1;

input Mgr $ Exception $ CNT ;

datalines;

Smith Transfer 1

Smith Transfer 1

Smith Cancel 1

Brown Reinstate 1

Brown Terminate 1

Saunders Transfer 1

Saunders Cancel 1

;run;

proc sort data=have1;by exception;run;

proc transpose data= have1 out=have2 (drop= _NAME_ _LABEL_) let prefix=mgr_;

id Mgr;

by Exception;

var cnt;

run;

I want to do a proc report.  However the Mgr may expand to more than 3 (currently Smith,Brown and Saunders) or it may contract. Currently I would need to hard-code each Mgr in the proc report.  Is there a way to dynamically display the Mgr side by side and account for changes dynamically ?? 

proc report data=have1 wrap style(column)={just=center};

title 'Summary';

column Exception Mgr cnt ;

define Exception / group style (column)=[cellwidth=100pt] "EXC TYPE";

Define ???MGR????

/*define /group style(column)=[cellwidth=100pt] "MANAGER";*/

define cnt / sum "Total" style(column)=[cellwidth=90pt];

rbreak after / summarize style (summary)= Header;

compute after;

Exception = 'Total';

endcomp;

run;

title ;title2;

run;

[ed_sas_member]

Hi @Q1983
I think you don’t need to transpose your data.
You can use the raw data directly.
As you’ve define the variable exception as the ´group‘ variable in the proc report, you can define the variable Mgr as the ‘across’ variable.

[ballardw · Accepted solution]

Consider

data have;
input Mgr $ Exception $ CNT ;
datalines;
Smith Transfer 1
Smith Transfer 1
Smith Cancel 1
Brown Reinstate 1
Brown Terminate 1
Saunders Transfer 1
Saunders Cancel 1
;
run;
proc report data=have     ;
   column Exception Mgr, cnt ;
   define Exception / group style (column)=[cellwidth=100pt] "EXC TYPE";
   Define mgr/ across;

   define cnt / sum "Total" style(column)=[cellwidth=90pt];
   rbreak after / summarize style (summary)= Header; 
   compute after;
      Exception = 'Total';
   endcomp;
run;

The comma between mgr and cnt on the columns statement says to "nest" the value or statistic for CNT with the value of MGR.

And the across define for mgr makes the columns based on the values of the MGR variable.

May want to suppress the labels for MGR and possibly CNT though.