Create multiple multi-tab Excel worksheets and email as attachments

[denperp]

Hi,

I’m using SAS 9.4.

My goal is to have a program to create multiple multi-tab worksheets in Excel.  The output should have each worksheet as a department and each worksheet/department could have 0-3 additional tabs (units).   l then want to email each worksheet to the respective supervisor.  

Bonus 1:  One unit is a bit rogue (SOLO) and doesn’t have a department but a unit with a supervisor.  I can manually do that one if overly complicated.

Bonus 2.  A few units/departments have an & in the name. I assume I need to use %SUPERQ but used tranwrd function in my program to replace ‘&’ with ‘and’ to avoid that.

Bonus 3: A few departments have subunits.  Those subunits would replace the units.  Again, I can do this programmatically in the prep part to replace a subunit with unit if need be.

Bonus 4:  Plant managers would also like a copy of the units but if I can get the bulk down, I can probably figure out the code for that.

/*getting departments in a list and the count*/

proc sql; select distinct department, count(distinct department)  into :dept_list1-  , :number_deps from employees where department ^=''; quit;

/*creating an excel sheet for each instance of a department*/
filename target '[filepath]\departments.xlsx';
ods excel file=target options (frozen_headers='on' embedded_titles='on'  autofilter="2" flow = 'table');



%macro helpme;
  ods results off;  
%do i=1 %to &number_deps;
  ods excel file="[filepath]\&&dept_list&i...xlsx";

    ods excel options(sheet_name="Dept - &&dept_list&i" flow = "tables" );
    proc print data=employees noobs; 
	var Employee_Identification department_name department;
	where department = "&&dept_list&i"; run;

  %end;
  ods excel close;

/*Need help here - can I add tab(s)/unit(s) to the existing &&dept_list&i worksheets the units where 
the department = &&deptlist&i?  */
%mend helpme;

%helpme


/*prepping for the email macro*/

/*list of supervisors*/

proc sql; select distinct employee_identification, email, Department into :SupervisorID1-, :SupervisorEmail1-, :SupervisorDepartment1- from employees
where departmental_supervisor = 'x';
quit;



proc options group=email; run;


options 
emailsys=smtp 
emailhost = [myhostname]
emailid = "[my email]"
emailport = 25;

/*then I am hoping with those excel files I can attach them to the correct supervisor....*/
/*this likely needs a lot of work*/

%macro email;

%do i = 1 %to %number_deps;

filename msg email ;
data _null_;
file mailbox EMAIL to="&&supervisoremail&i"
 					from="myemail@company.com"
					subject = "Employee List ";
		put "Hello,";
		put "This is the report of your employees";
		put "with an attached file";
		put;
		put 'Regardss';
		put
		put 'My name'

		FILENAME MailBox EMAIL ATTACH="[filepath/]&&SupervisorDepartment1&i";
		run;

%end;

%mend email;

%email

Thank you!

[Mazi]

Hi,

Can you clarify something for me?

You want one excel workbook named departments, and different tabs for each department inside that workbook.

Is that correct?

[denperp]

Hi, thanks for looking at this. 

No, each department will be its own workbook and a tab with the department name.  I have this part down for the most part. 

In the example data - there are 23 departments.  The %macro helpme creates 23 workbooks with the name DEPT (BCN, BRDSTCK, CHZPUFF, CRACK, etc).  I added the prefix 'DEPT- ' to each department on the tabs (BCN, DEPT-BRDSTCK, DEPT-CHZPUFF, DEPT-CRACK, etc).  

What I am hoping to do is put additional tabs in those workbooks based on the units [Unit].  In my example file the department CHZPUFF has two units -CHZPFLS and CHZPFRG so those units would be their own tabs in the CHZPUFF workbook.  The TST department has three units (TST-Jam, TST-Butter, TST-PLN).  Many departments have only one unit, some have none, some have two or three, but I still want those units to be a separate tab because the employees are different. 

Then, each workbook would be emailed to the supervisor of the department.  I hope that helps clarify?

I'm reattaching the workbook.

[ballardw · Accepted solution]

You are most of the way there.

In this part:

%do i=1 %to &number_deps;
  ods excel file="[filepath]\&&dept_list&i...xlsx";

    ods excel options(sheet_name="Dept - &&dept_list&i" flow = "tables" );
    proc print data=employees noobs; 
	var Employee_Identification department_name department;
	where department = "&&dept_list&i"; run;

  %end;
  ods excel close;

 you would add additional ODS EXCEL statements with the Sheet_name and the procedure to generate the output before the %end; . If the sub-unit names are in another variable you could use the Sheet_interval="By Group" to get a separate tab for each sub-unit and use By group processing in the procedure(s). Might be a  good idea to sort the data by the department and sub-unit variables.

[denperp]

Thanks.  I'm getting closer and I think I need to prep my data a bit more.  One issue I am having is some departments have units and some don't - so while it may be a bit clunkier it should work to do this in 2 steps - one with just departments and one with the units. 

[Tom]

What is the impact of not having units?

Is UNIT the thing that gets split into different worksheets?

If so then set UNIT to a constant for the departments that do not have units.  Perhaps something like ALL.

Department Unit
DEPT_ONE UNIT_ONE
DEPT_ONE UNIT_TWO
DEPT_TWO ALL

[Tom]

You have two separate problems.

1) How to create multiple XSLX Workbooks that each have multiple worksheets.  (A TAB in a WORKBOOK is a WORKSHEET.)

2) How to send an email with an attachment.

If you want to use macro code to help with this I would recommend making a macro that takes as an input parameter the name of the department. 

%macro email_report(department);
....
%mend;

Since you mentioned that the department names might not be macro friendly just assume the user has passed in the department value as a string literal.  So the calls would look something like this:

%email_report(department='R&D')

And in the body of the macro you would use the department parameter something like this:

where department=&department ;

that way and & or % characters in the name will be ignored by the macro processor since they are inside a string literal bounded by single quotes.

So assuming you have an input dataset named EMPLOYEES then it should be sorted by DEPARTMENT so that you can use it to generate one call to the macro per department with a simple data step like this:

data _null_;
  set employees;
  by department;
  if first.department;
  call execute(cats('%nrstr(%email_report)(department=',quote(trim(department),"'"),')'));
run;

Now you just need to answer the first two questions to decide how to build the actual macro.

For the first one just use ODS features to generate a separate worksheet for each value of the BY group.  Look up Sheet_interval 

For the second one just look-up how to attach an existing file to an email.  Look at the attach= option of EMAIL fileref engine 

As to where to send the email, if that information is in the EMPLOYEE dataset then use it to generate the code that sets the TO address of the email.  Perhaps by storing the value into a macro variable.

[denperp]

Thank you and for those references!