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
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
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.