topic proc tabulate to create multiple reports by group in SAS Programming

proc tabulate to create multiple reports by group

Ronein — Wed, 01 Apr 2020 11:47:05 GMT

Hello

I have a raw data with loans information in year 2020 from 01.01.2020 until 31.03.2020.
There are following fields in the row table:
Customer_ID
Date_of_loan
Sum_loan
sector

Then I need to add 2 new calculated fields:
day_loan is the day from field date_of_loan (for example for date 13FEB2020 the day is 13)
month_loan is the month from field date_of_loan(for example for date 13Feb2020 the month is 2)

The task is to create for each sector a summary report that will have the following fields:
day_loan
Sum_loan in month 1 (Jan)
Sum loan in month 2 (Feb)
Sum loan in month 3 (March)

I have 2 questions:
1- Is there a way to use proc tabulate to create multiple tables ?(Here I create a macro var and write manually all sectors that appear in the raw table but I want to find a more clever way )
2-Is there a way to export the multiple summary tables to one excel sheet with side by side structure?

I cannot copy paste from SAS so I need to type the code manually here (apologize)

%let Vector=Cars+Real_Estate+Shops+Factories;
%let k=4;

%macro tabulate;
%Do  j=1  %TO  &k.;
%let sector=%SCAN(&vector.,&j.,+);
Title;
Title "&sector.";
proc tabulate data=Rawtbl (Where=(sector=&sector.));
class  day_loan  month_loan;
VAR sum_loan;
table day_loan='',sum_loan=''*(Sum='sum of loans in $')*month_loan='month'/box='day';
Run;
%end;
%mend;
%tabulate;

Re: proc tabulate to create multiple reports by group

Kurt_Bremser — Wed, 01 Apr 2020 12:08:41 GMT

You can combine by and class to produce several reports in one run:

proc sort
  data=sashep.cars
  out=cars
;
by origin;
run;

proc tabulate data=cars;
class make;
by origin;
var msrp;
table make*msrp;
run;

Re: proc tabulate to create multiple reports by group

ballardw — Wed, 01 Apr 2020 15:58:31 GMT

Or use one or more Class variables to create a "Page" level, then you get one table per level of the Page expression:

proc tabulate data=sashelp.cars;
	class make;
	class origin;
	var msrp;
	table origin,      /* <= Page*/
	      make*msrp,   /* <= Row */
	      sum            /* <= Column */
	;
run;

And Proc Tabulate will allow multiple TABLE statements in a single proc call.

proc tabulate data=sashelp.cars;
	class make;
	class origin;
	class type;
	var msrp;
	table origin,      /* <= Page*/
	      make*msrp,   /* <= Row */
	      sum          /* <= Column */
	;
	table origin,      /* <= Page*/
	      make*msrp,   /* <= Row */
	      type *mean         /* <= Column */
	;

run;