How do you print to sheets by group with some variables in the titles

yelkenli — Tue, 26 Jul 2022 15:42:40 GMT

I have the below code and output, but would like to remove some of the variables in the printed tables and put in a series of titles at the top of each worksheet.

Can this be done in one proc print step, or do I need some looping program?

ods tagsets.excelxp file="user/high_runs.xml" 
			style=styles.plateau
			options(sheet_interval='BYGROUP' sheet_name='#byval1');
proc print data=high_runs;
    by high_group;
run;
ods tagsets.excelxp close;

the output (the high_runs dataset would be this table, but with the high_group variable)

prod_name prod_number prod_type_name prod_cost local_dttm qty_a qty_b
prod1 1001 small 23 02MAY2021:21:10:00.000000 165 373
prod1 1001 small 23 02MAY2021:21:20:00.000000 381 555
prod2 1002 med 24 02MAY2021:21:30:00.000000 414 658
prod2 1002 med 24 02MAY2021:21:40:00.000000 680 1106
prod2 1002 med 24 02MAY2021:21:50:00.000000 635 1052
prod2 1002 med 24 02MAY2021:22:00:00.000000 517 812
prod3 1003 large 25 02MAY2021:22:10:00.000000 641 1043
prod3 1003 large 25 02MAY2021:22:20:00.000000 681 1642
prod3 1003 large 25 02MAY2021:22:30:00.000000 878 1689
prod3 1003 large 25 02MAY2021:22:40:00.000000 1130 1547
prod3 1003 large 25 02MAY2021:22:50:00.000000 1294 1477

Wanted:

prod_name: prod1
prod_number: 1001
prod_type_name: small
prod_cost: 23

local_dttm qty_a qty_b
02MAY2021:21:00:00.000000 69 173
02MAY2021:21:10:00.000000 165 373

next worksheet
prod_name: prod2
prod_number: 1002
prod_type_name: med
prod_cost: 224

local_dttm qty_a qty_b
02MAY2021:21:20:00.000000 381 555
02MAY2021:21:30:00.000000 414 658
02MAY2021:21:40:00.000000 680 1106
02MAY2021:21:50:00.000000 635 1052

Re: How do you print to sheets by group with some variables in the titles

ballardw — Tue, 26 Jul 2022 16:22:00 GMT

See if this gives you some clues:

Proc sort data=sashelp.class out=work.class;
   by sex age;
run;

options nobyline;
Title "Class report";
title2"#bvar1: #byval1";
title3"#byvar2: #byval2";

proc print data=work.class noobs;
   by sex age;
run;

options byline;

Since you can have 9 title statements you have a fair amount of lee-way to play with if you want multiple variables "removed" from the body to a separate line each. So sort your data by the 4 variables and use all 4 on the By statement. The option nobyline suppresses the default behavior of including BY var1 var2 in the output so you provide what you want using #byval and #byvar.

Note if your byvar names aren't as pretty as you want for the report you could provide any text you want instead of #byvar

prod_name	prod_number	prod_type_name	prod_cost	local_dttm	qty_a	qty_b
prod1	1001	small	23	02MAY2021:21:10:00.000000	165	373
prod1	1001	small	23	02MAY2021:21:20:00.000000	381	555
prod2	1002	med	24	02MAY2021:21:30:00.000000	414	658
prod2	1002	med	24	02MAY2021:21:40:00.000000	680	1106
prod2	1002	med	24	02MAY2021:21:50:00.000000	635	1052
prod2	1002	med	24	02MAY2021:22:00:00.000000	517	812
prod3	1003	large	25	02MAY2021:22:10:00.000000	641	1043
prod3	1003	large	25	02MAY2021:22:20:00.000000	681	1642
prod3	1003	large	25	02MAY2021:22:30:00.000000	878	1689
prod3	1003	large	25	02MAY2021:22:40:00.000000	1130	1547
prod3	1003	large	25	02MAY2021:22:50:00.000000	1294	1477

prod_name: prod1
prod_number: 1001
prod_type_name: small
prod_cost: 23

local_dttm	qty_a	qty_b
02MAY2021:21:00:00.000000	69	173
02MAY2021:21:10:00.000000	165	373

next worksheet
prod_name: prod2
prod_number: 1002
prod_type_name: med
prod_cost: 224

local_dttm	qty_a	qty_b
02MAY2021:21:20:00.000000	381	555
02MAY2021:21:30:00.000000	414	658
02MAY2021:21:40:00.000000	680	1106
02MAY2021:21:50:00.000000	635	1052

topic Re: How do you print to sheets by group with some variables in the titles in ODS and Base Reporting

How do you print to sheets by group with some variables in the titles

Re: How do you print to sheets by group with some variables in the titles