I want to mimic a Proc Tabulate report using Proc Report. I am failing.

[jwillis]

Our current code uses a Proc Tabulate to produce a report.  I want to convert the Tabulate to Proc Report so that I can use Compute blocks and if then processing.  The tabulate report is attached.  The tabulate displays, for each row of Plant_Tech, two groups of Area. Each Area group column contains three columns. The Proc Report does not need the Plant_Tech variable in the column statement since there is only one value for Plant_Tech.

Tabulate Code:

proc tabulate data=&libloc..overall;

where (plant_tech="&pltech") and (area in ("Internal Post Release","Distribution")) and (&dat12 <= jj_year_month <= &dat1);

  class plant_tech area jj_year_month ;

  classlev plant_tech area jj_year_month ;

  var count_nc count_nc_lots count_tested_plus_nc_lots;

  table jj_year_month='J&J Year/Month'*format=dat.,

  plant_tech='Plant/Technology'

          *area='Area'*(count_nc='No. NCs'*sum=''*f=6.0

                        count_nc_lots='No. NC Lots' *sum=''*f=6.0

                        count_tested_plus_nc_lots='No. Lots Actioned'*sum=''*f=6.0)

     ;

run;

Tabulate Output:

proc report data=temp.overall nowd;

where (plant_tech="&mfg")  and (area in ("Internal Post Release","Distribution")) and (&dat12 <= jj_year_month <= &dat1);

  columns ("Plant/Technology:    &mfg." (Area (jj_year_month (count_nc count_nc_lots count_tested_plus_nc_lots))));

        define Area                   / order                               width=25;                        

        define jj_year_month   / display   'Year/Month'  ; 

        define count_nc           / display 'No. NCs'         format=6.0;

        define count_nc_lots   / display 'No. NC Lots'     format=6.0;

        define count_tested_plus_nc_lots / display 'No. Lots Actioned' format=6.0;

run;

[ballardw]

Have you tried using the report window to try to mimic the design layout. Then save an modify code generated?

[Cynthia_sas · Accepted solution]

Hi:

  The structure of your data is going to really impact how you do the PROC REPORT code. Can you post what is in your OVERALL dataset or mock up some test data?

  However, I am guessing that you would want PLANT_TECH and AREA to both be ACROSS usage (not ORDER usage) -- and, BTW, I don't see PLANT_TECH on the COLUMN statement. Then I would also guess that you would want JJ_YEAR_MONTH to be a GROUP variable (not DISPLAY). And, I would guess that you're getting an error on your PROC REPORT step, or at least undesirable output.

  Cynthia

  Without data to test, this code is just a guess, but I'm thinking that you probably want something more like this, with JJ_YEAR_MONTH as the first item going down the rows -- GROUP usage and then PLANT_TECH and AREA as ACROSS items, with PLANT_TECH the first ACROSS item and with AREA the second ACROSS item (which would mimic your TABULATE crossing of PLANT_TECH*AREA). Then you would have your "count" variables with usages of SUM (so you could have a GRAND TOTAL row if you wanted -- those would be nested underneath the unique combinations of PLANT_TECH and AREA). Remember that the comma operator in PROC REPORT does nesting in the columns the way that the * works in PROC TABULATE. The thing that you can't do with PROC REPORT is use the comma to get nesting in the rows. That happens automatically with PROC REPORT based on the order of the items in the COLUMN statement. But since you only have JJ_YEAR_MONTH in the row dimension in TABULATE, then you only need it to be first in the COLUMN statement in PROC REPORT.

proc report data=&libloc..overall nowd split='*';

  title 'PROC REPORT';

  column jj_year_month plant_tech,area,(count_nc count_nc_lots count_tested_plus_nc_lots);

  define jj_year_month / group 'J&J Year/Month' style(column)=Header;

  define plant_tech / across 'Plant/Technology';

  define area / across 'Area';

  define count_nc / sum f=6.0 'No. NCs';

  define count_nc_lots / sum f=6.0 'No. NC Lots';

  define count_tested_plus_nc_lots / sum f=6.0 'No. Lots Actioned';

run;

[jwillis]

BallardW,

Thank you.  I never learned how to use the Proc Report Windowing environment.

Cynthia,

Thank you.  You provided what I needed.  I rely on many of the Report papers that you've written.  Please keep at it!  I was trying to follow Paper 388-2014 "Sailing Over the ACROSS Hurdle in PROC REPORT" using "Output 8" as my guide but I was failing. When I had the column statement correct, I was incorrectly coding the define statements.  When my define statements were correct, my column statement was wrong.  The statements below work perfectly.

  columns jj_year_month Plant_Tech,area,(count_nc count_nc_lots count_tested_plus_nc_lots);

        define jj_year_month                       / group    'J&J Year/Month'       format=dat. width=15;

        define plant_tech                             / across   'Plant/Technology'    width=25;                        

        define Area                                      / across   'Area'                          width=25;                        

        define count_nc                               / analysis 'No. NCs'                   format=6.0;

        define count_nc_lots                       / analysis 'No. NC Lots'            format=6.0;

        define count_tested_plus_nc_lots / analysis 'No. Lots Actioned'  format=6.0;

[jwillis]

Thank you all.  I have my solution.  When I applied Cynthia's solution, the report worked however the "dat." formatting was arranging the rows in the wrong order.  Using ideas I've found in Cynthia's papers, I applied the following code to achieve exactly what I need.  The first, non printed, group statement sorts the Year Month rows correctly. The second, printed, group statement displays the formatted Year Month correctly. 

columns jj_year_month=test1 jj_year_month Plant_Tech,area,(count_nc count_nc_lots count_tested_plus_nc_lots);

define test1                                      / group 'J&J Year/Month 1 ' noprint;

define jj_year_month                        / group 'J&J Year/Month' format=dat.;

define plant_tech                              / across 'Plant/Technology' width=25;

define Area                                       / across 'Area' width=25;

define count_nc                                / analysis 'No. NCs' format=6.0;

define count_nc_lots                        / analysis 'No. NC Lots' format=6.0;

define count_tested_plus_nc_lots  / analysis 'No. Lots Actioned' format=6.0;

[jwillis]

An order=data statement in the define statement also retains the original sort order.