create multiple reports from long table

[Ronein]

Hello

I have a long table with summary statstics for multiple metrics.

I want to create a summary report that put each summary alone and with difference of one row between the reports.

Here is how the report should look like-

as i said one row gap between reports and column headers in yellow

proc sql;
create table t1 as
 select 'region' as VAR,
 region as category,
 'sales' as metric,
 count(sales) as nr_obs,
 sum(sales) as amnt format=best.
 from sashelp.shoes
 group by region;
 quit; 

 proc sql;
 create table t2 as
 select 'region' as VAR,
  region as category,
  'net_sales' as metric,
 count(sales) as nr_obs,
 sum(sales-returns) as amnt 
 label='Net Sales Ratio'
 from sashelp.shoes
 group by region;
 quit; 

 proc sql;
  create table t3 as
 select 'region' as VAR,
  region as category,
  'net_sales_Ratio' as metric,
   count(sales) as nr_obs,
  sum(sales-returns)/sum(sales) as amnt format=8.2
 label='Net Sales Ratio'
 from sashelp.shoes
 group by region;
 quit; 

 Data all;
 length metric $100.;
 set t1 t2 t3;
 Run;

[PaigeMiller]

SQL is not a good choice for reporting. SAS has a number of PROCs specifically designed for reporting, such as PROC PRINT, PROC REPORT and PROC TABULATE, which also allow the color yellow (or any other color) to be applied where desired, and have many other advantages over SQL when creating reports. Maxim 7 (there is a procedure for it). Maxim 10 (SQL may eat your time). Maxim 14 (use the right tool). 

As long as you are being picky about having a blank space between your tables and yellow headers, you should also be picky and use proper English for the column headers and values. You ought to capitalize things like Metric, Category, Region, Sales, Net_Sales, Net_Sales_Ratio, this would make the report seem even more well done and professional. You should also use plain English whenever possible, net_sales is not an English word or phrase, but Net Sales is plain English, and it would improve the appearance of the report! Use the COMMA or COMMAX format for long integers like in your table. Use the PERCENT format for percents. Replace amnt with an actual descriptive word! Produce a report that is easy to read!

Example:

ods excel file='temp.xlsx' options(sheet_interval='NONE');
proc print data=sashelp.class style(header)=[backgroundcolor=yellow color=black] 
    style(obsheader)=[backgroundcolor=yellow color=black];
    where sex='M';
    id sex/style(data)=[backgroundcolor=white color=black];
    var name age height weight;
run;
proc print data=sashelp.class style(header)=[backgroundcolor=yellow color=black] 
    style(obsheader)=[backgroundcolor=yellow color=black];
    where sex='F';
    id sex/style(data)=[backgroundcolor=white color=black];
    var name age height weight;
run;
ods excel close;   

[Ronein]

My question was how to perform it from the long table that In my question . In real life the long table will be much longer

[PaigeMiller]

Everything I said scales to longer tables.

[Ksharp]

proc sql;
create table t1 as
 select 'region' as VAR,
 region as category,
 'sales' as metric,
 count(sales) as nr_obs,
 sum(sales) as amnt format=best.
 from sashelp.shoes
 group by region;
 quit; 

 proc sql;
 create table t2 as
 select 'region' as VAR,
  region as category,
  'net_sales' as metric,
 count(sales) as nr_obs,
 sum(sales-returns) as amnt 
 label='Net Sales Ratio'
 from sashelp.shoes
 group by region;
 quit; 

 proc sql;
  create table t3 as
 select 'region' as VAR,
  region as category,
  'net_sales_Ratio' as metric,
   count(sales) as nr_obs,
  sum(sales-returns)/sum(sales) as amnt format=8.2
 label='Net Sales Ratio'
 from sashelp.shoes
 group by region;
 quit; 

 Data all;
 length metric $100.;
 set t1 t2 t3;
 Run;
 proc sort data=all;
 by metric;
 run;

 options nobyline;
 ods excel file='c:\temp\temp.xlsx' options(sheet_interval='none');
 proc report data=all nowd;
 by metric;
 column  metric var category  nr_obs amnt;
 define _all_ /display;
 run;
 ods excel close;