Send emails to different users from SAS with embedded table that has unique user data from dataset

[Ryan_G]

Hello SAS Gurus!

Please see the attached excel file that has the mock data to use for this.  I need to send individual emails to different users based on their schedule selections.  The attached file is the base table where all the data resides.  What I need to do is embed,  for each user, what their schedule selections were (could have varying ranges 1- whatever) into the email (preferably as a table).  So for example,   EMPID (unique identifier field) "0001" has submit 2   schedules,  I need to imbed both of those from this table,  whereas  EMPID "0006" has 6 different selections and I'd need to include each of those in their email.

Part A:  I'm not sure if this is the right/most efficient way to achieve this (open to any better solutions),  but my thought process is to create unique SAS datasets for each of these users (so EMPID "0001" has their own dataset with their 2 schedules, and so on).   I've done this using the code in Part A below.  This is a loop and works to create a table for each distinct EMPID.

Part B:  My next thought is to now pair this with code I'd see in prior posts,  that allow you embed a table into an email.  I can get this to work by itself, but I somehow need to partner this up with the macro in Part A,  so that as it creates each unique table, it's putting that into the email and sending respectively to each individual with their appropriate data, looping until all unique users have been sent their individual email with their specific schedule information.

Greatly appreciate any insight/wisdom in how to make this work or an alternative method that accomplishes the same.

/**** PART A ******/ 

Data Import_Sched_mock2;

set Import_Sched_mock;

EMPID_N = "A"||EMPID;

IF EMAIL_ADDR = "" THEN DELETE;

run;

proc sql ;

select distinct EMPID_N,count(distinct EMPID_N) into :EMPID_N1-,:c

from Import_Sched_mock2;

quit;

%macro t;

proc sql;

%do i =1 %to &c;

create table &&EMPID_N&i as

select *

from Import_Sched_mock2

where EMPID_N="&&EMPID_N&i";

%end;

quit;

%mend t;

%t

/**** PART B ******/ 

%macro send_report_emails();

%do i=1 %to &EMPID_N;

filename mymail email

type='TEXT/HTML'

subject="TEST EMAILS-RG"

to=&EmailTo

cc=&EmailTo

from=&EmailFrom

importance="HIGH";

data _null_;

file mymail;

/*ODS html body=temp style = noline; */

ODS html body=MYMAIL style = noline;

ods html text = "Good Morning,";

ods html text = "";

/***** need to figure out this part of the code to have it read the variable table names *******/

PROC REPORT DATA= Import_Sched_mock2 nowd HEADLINE HEADSKIP

style (report) = {background = white

font_face = "Verdana" font_size = 7pt just=left }

style (column) = {background = white CELLHEIGHT = 2.5%

font_face = "Verdana" font_size = 7pt just=left}

style (header) = {foreground = cx5e2750 font_face="Verdana"

font_size = 8pt just=left

background = white} ;

columns

Schedule_Name

Type

Start_Time

End_Time;

DEFINE Schedule_Name / 'Schedule Name';

define Type / 'Type';

define Start_Time / "Start Time";

define End_Time / "End Time";

run;

ods html text = "Have a Great Day.";

ods _all_ close;

RUN;

%end;

%mend send_report_emails;

%send_report_emails;

[Ryan_G · Accepted solution]

I was able to resolve this by modifying Part B utilizing the macro I created in Part A.  It now runs each part in parallel creating the desired individual emails!

Solution

PROC REPORT DATA= &&EMPID_N&i nowd HEADLINE HEADSKIP