Search code examples
excelsasemail-attachments

How to split excel data by region and mail fractions to appropriate addresses from SAS?

I have prepared a dataset in sas and exported in the .xlsx format to a location on my computer. I'm able to send mail with the attachment which i have exported. I wanted to know if there's a possibility to apply a 'where' condition to a column(Region in this case,having 4 values -W,E,S,N) in the excel via sas programming and attach the filtered (specific records , like data for W,S only etc) excel file to an appropriate email address? Kindly help.I'm attaching the code written on SAS Enterprise Guide 7.1 for single recipient which sends the unfiltered excel and the image of the sample data. enter image description here

options emailsys = smtp
    emailid = 'xxxx@gmail.com'
    emailpw = 'xxxx'
    emailhost = 'smtp.gmail.com'
    emailport = 587 ;

options emailhost=(
    'smtp.gmail.com'
    STARTTLS auth=LOGIN
    id='xxxx@gmail.com'
    pw='xxxx' port=587
);
filename f_email email
    to = 'xxxx@jkl.com'
    from = 'xxxx@gmail.com'
    subject = 'Test'
    attach=("D:\abc\xxxx.xlsx"  content_type="application/xlsx");

data _null_;
    file f_email;
    put 'Test';
run;

Regards, sklal

Solution

  • You can't filter the excel file before sending it, You'll have to create one excel file for each region and attach it separately.

    Steps:

    1. Read the excel file in SAS and save to a work table
    2. Filter/breakdown the table in to four tables: north,south,east, west
    3. Export your four tables to four excel files
    4. Repeat the above code four times or create a macro function for your email code.