PROC REPORT - Summarising Data Using Different Classification Variables

[Scott_Mitchell]

Good Afternoon All,

SAS Version :  9.4M4

OS: Windows Server 2012 R2

I haven't used PROC REPORT in quite some time so need a little refresher.

I have obtained results for the first report table I am looking to output (please see the below code - SYNTAX 1), but am having a few problems with adding subsequent tables.

SYNTAX 1

/*CREATE EXAMPLE DATA*/
DATA SHOES ;
	SET SASHELP.SHOES (IN = A KEEP = PRODUCT SUBSIDIARY SALES)
		SASHELP.SHOES (IN = B KEEP = PRODUCT SUBSIDIARY SALES);
	
	IF B AND _N_ = 396 THEN ITEM = 0;

	IF B THEN DO;
	     SALES = SALES *.15;
	     SUBSIDIARY = "London";
	END;

	ITEM + 1;

	BOOTS 		= 1;
	MEN_CASUAL	= 1;
	MEN_DRESS   = 1;
RUN;


OPTIONS  LEFTMARGIN = .5IN RIGHTMARGIN = .5IN ORIENTATION = LANDSCAPE CENTER MISSING="" NODATE NONUMBER;

ODS LISTING CLOSE;
ODS HTML CLOSE;
ODS PDF FILE= "&WORKPATH.\SHOES.PDF" UNIFORM;

PROC REPORT DATA = SHOES OUT = TESTING SPLIT = '*' NOWD ;
	COLUMNS ITEM PRODUCT SUBSIDIARY SALES SALES = BOOTSSALES SALES = MENCASUALSALES SALES = MENDRESSSALES BOOTS MEN_CASUAL MEN_DRESS
			BOOTS MEN_CASUAL MEN_DRESS;
	DEFINE ITEM  		  / GROUP "Item";
	DEFINE PRODUCT 		  / GROUP;
	DEFINE SUBSIDIARY 	  / GROUP;
	DEFINE BOOTS		  / GROUP NOPRINT;
	DEFINE MEN_CASUAL	  / GROUP NOPRINT;
	DEFINE MEN_DRESS   	  / GROUP NOPRINT;
	DEFINE BOOTSSALES 	  / SUM NOPRINT;
	DEFINE MENCASUALSALES / SUM NOPRINT;
	DEFINE MENDRESSSALES  / SUM NOPRINT;

   	COMPUTE ITEM;
		ITEM2 = ITEM;
		IF MOD(ITEM2,2) THEN DO;
         	CALL DEFINE(_ROW_, "STYLE", "STYLE=[BACKGROUND=#f2f2f2]");
      	END;
   	ENDCOMP;


	COMPUTE SALES;
		IF	UPCASE(TRIM(LEFT(PRODUCT))) = "BOOT" THEN DO;
			H_CNTBOOTS+1;
			H_SALESBOOTSCASH+SALES.SUM;
		END;

		ELSE IF UPCASE(TRIM(LEFT(PRODUCT))) = "MEN'S CASUAL" 	 THEN DO;
			H_CNTMENCASUAL+1;
			H_SALESMENCASUAL+SALES.SUM;
		END;

		ELSE IF UPCASE(TRIM(LEFT(PRODUCT))) = "MEN'S DRESS" THEN DO;
			H_CNTMENDRESS+1;
			H_SALESMENDRESS+SALES.SUM;
		END;
	ENDCOMP; 

	COMPUTE AFTER;
		LINE @1 " ";
		LINE @1 "NUMBER OF BOOT SALES :" @34 H_CNTBOOTS ;
		LINE @1 "VALUE OF BOOT SALES :" 	@34 H_SALESBOOTSCASH DOLLAR18.2;
		LINE @1 "NUMBER OF MEN'S CASUAL SALES :" @34 H_CNTMENCASUAL; 
		LINE @1 "VALUE OF MEN'S CASUAL SALES :" @34 H_SALESMENCASUAL DOLLAR18.2;;
		LINE @1 "NUMBER OF MEN'S DRESS SALES :" @34 H_CNTMENDRESS;
		LINE @1 "VALUE OF MEN'S DRESS SALES :" @ 34 H_SALESMENDRESS DOLLAR18.2;;
		LINE @1 " ";
	ENDCOMP;

RUN;

ODS PDF CLOSE;
ODS LISTING;
ODS HTML;

I want a table similar to the one generated by the below syntax (SYNTAX 2), but was unsure if there was a way to produce the same output using a single PROC REPORT.  My concern is merely processing time to read the dataset when each PROC REPORT executes.  I guess this could be overcome by reading the data into memory using the SASFILE STATEMENT.

SYNTAX 2

PROC REPORT DATA = SHOES OUT = TESTING SPLIT = '*' NOWD ;
	COLUMNS SUBSIDIARY SALES;
	DEFINE SUBSIDIARY 	  / GROUP;
	DEFINE SALES 		  / SUM;
RUN;

Any opinions\insight would be greatly appreciated.

Thanks

Scott Mitchell

[Cynthia_sas · Accepted solution]

Hi:
  If you are worried about processing time, you could remove the OUT= option on both PROC REPORT statements and the UNIFORM option on the ODS PDF statement.  

  You're going to need to generate the output using 2 PROC REPORTS because of the need for different variables in each COLUMN statement.

  I'm not sure that what you're doing in the COMPUTE block is entirely necessary. You could create "count" variables in your DATA step program too, using 0 and 1 when you have a product that you want to count and that would save overhead in the PROC REPORT step. I don't see that the second PROC REPORT is going to have a lot of overhead, other than the OUT=, but if you want it in the same PDF file as the first report, then you need to move it inside the ODS PDF "sandwich" or if you want it as a separate PDF file, then give the second step a separate set of ODS PDF statements.

Cynthia

[Scott_Mitchell]

Hi @Cynthia_sas,

As always you are a star.  Your insights confirm my suspicions, but was hoping there was another way of approaching it.  There are 3 reports per segment, so I was hoping to reduce the amount of code, alas that is unavoidable.

For completeness sake:  Testing was simply used whilst developing the report, which was always going to be removed anyway.  Unfortunately I can't remove the UNIFORM option as it is a requirement tables across pages be sized the same.

Thank you again for your reply.

Regards,

Scott