topic Re: Byval in header and Excel tab in ODS and Base Reporting

Byval in header and Excel tab

abcrandy — Fri, 20 Jul 2018 18:43:41 GMT

I have an excel report that I need to automate via SAS. Once exported, the report should have a tab for every month where data exists. The tab and header should indicate the month name. Currently I have everything working except the month name. The report currently displays the month number instead. Below is my current SAS Code and a screenshot of the current output:

%macro get_report();
	ods excel file='/prd/saucgo/reporting/summary/output/Monthly_Schedule.xlsx' 
	options(sheet_name='#byvar' /*'#byval1'*/ embedded_titles='yes' embedded_footnotes='yes'
		 tab_color='purple' start_at='2,4' frozen_headers='yes'
		 title_footnote_nobreak='yes' autofilter='1-10' absolute_column_width='16');
	title 'Monthly Schedule' ;
/*	footnote 'This footnote will appear in the worksheet';*/

	proc report data=Monthly_Schedule;
		by Mo;
		column Origin Destination Flight_Number	Depart_Time	Arrival_Time Aircraft Start_Date End_Date DOW_Ops Group;
		define Flight_Number / display 'Flight#';
		define SCH_AC_DPRT_TIM  / display 'Depart_Time';
		define SCH_AC_ARR_TIM / display 'Arrival_Time';
		define Yr / group;
		define Mo / group;
	run;

	proc report data=_Mth_Schd;
		column sch_dprt_stn_cd sch_arr_stn_cd LegStartDte LegEndDte Body;
		define sch_dprt_stn_cd / display 'Departure';
		define sch_arr_stn_cd / display 'Arrival';
		define LegStartDte / display 'Leg Start Date';
		define LegEndDte / display 'Leg End Date';
		ods excel close;
%mend;

Re: Byval in header and Excel tab

ballardw — Fri, 20 Jul 2018 19:42:40 GMT

Generally if the variable is numeric and you want to display text such as "January" then you need to have a format and apply it to the variable. Such as:

proc format;
value mo
1='January'
2='February'
/* follow the pattern to*/
12='December'
;
run;

and add: Format mo mo.; /* the second with the period is the name of the format*/

to the proc report code. The by line would then read "MO=July".

If add a label to the MO variable such as: Label mo='Month'; then the by line would read "Month=July".

I don't spend a lot of time worrying over tab names in Excel so I'm not sure if that will help but as a minimum since your last proc doesn't have a by variable you likely need to insert an additional ODS EXCEL options(sheet_name='something'); to set the name for that one.

Re: Byval in header and Excel tab

abcrandy — Fri, 20 Jul 2018 20:29:01 GMT

Thanks ballardw . That worked like a charm! One last thing. How do I remove the "MO=" to only show the month name?

Re: Byval in header and Excel tab

ballardw — Fri, 20 Jul 2018 20:55:46 GMT

@abcrandy wrote:

Thanks ballardw . That worked like a charm! One last thing. How do I remove the "MO=" to only show the month name?

Easiest might be an additional title statement coupled with the nobyline option.

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

options nobyline;
title ' #byval(sex) ' ;
proc print data=work.class noobs;
  by sex;
  var name;
run; title;

options byline;

use a title with a higher number such as title2 than the ones you are currently using to make sure that if follows your existing one.

Don't forget to reset the option byline to return to the default behavior.

the last title statement clears the title so you don't get that title on other output.