BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
abcrandy
Fluorite | Level 6

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;

MonthlySchedule.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

3 REPLIES 3
ballardw
Super User

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.

abcrandy
Fluorite | Level 6

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

ballardw
Super User

@abcrandy wrote:

Thanks  . 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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1831 views
  • 0 likes
  • 2 in conversation