Data: Export Email to Excel

Reference: https://support.office.com/en-us/article/back-up-your-email-e5845b0b-1aeb-424f-924c-aa1c33b18833

Steps:

• open Outlook
• Move the emails that you want to export to a folder
• Go to the folder
• File -> Open & Export -> Import and Export
• Choose an action to perform: ->Export to a file -> Next
• Create a file of type: -> Comma Separated Values
• Select folder to export from: (The folder that you were in are highlighted) -> Next
• Save exported file as: -> Browse -> select folder and create file name -> Next
• The following actions will be performed: Export “Email messages” from folder: xxxxxxxx -> Map Custom Fields -> Finish
  • Subject
  • Body
  • From: (Name)
  • From: (Address)
  • From: (Type)
  • To: (Name)
  • To: (Address)
  • To: (Type)
  • BCC: (Name)
  • BCC: (Address)
  • BillingInformation
  • Categories
  • Importance
  • Mileage
  • Sensitivity

SAS: Export to Excel with Label Option and 9.3/9.4 Difference

It is common to export SAS data tables or outputs to Excel spreadsheets.  The default Excel output will only contain field names in the first row of the spreadsheet.  The ‘label’ option will allow the SAS label names to be the column names on the first row of the exported table.

Method 1:

PROC EXPORT DATA=table1.
OUTFILE= “C:\Work\SAS\table1.xlsx”
label DBMS=EXCEL REPLACE;
SHEET=”table1″;
NEWFILE=YES;
RUN;

method 2:

libname excelout “X:\Work\excelfile.xlsx”;

data excelout.sheet1 (dblabel=yes) ;
set tablename;

run;

libname excelout clear;

9.4 SAS Difference

PROC EXPORT

• Experience slowness in running PROC IMPORT and PROC EXPORT
• Find reference: https://blogs.sas.com/content/sasdummy/2012/05/01/64-bit-gotchas/;
• Change: DBMS= EXCEL to DBMS=EXCELCS

PROC EXPORT DATA= SASHELP.CARS 
OUTFILE= "C:\Excel\car.xlsx" 
            DBMS=EXCELCS REPLACE;
     SHEET="car"; 
RUN;

• Bug: a blank tab (_SAS_empty_) with A1 = “_empty_(CHECK_OTHER_SHEET) was generated in the xlsx file.

Solution: https://communities.sas.com/t5/SAS-Programming/Proc-Export-creating-a-blank-SAS-empty-sheet-in-the-exported/td-p/483623

• Change DBMS=EXCEL to DBMS=XLSX and the blank tab will disappear.

PROC EXPORT DATA= SASHELP.CARS 
OUTFILE= "C:\Excel\car.xlsx" 
            DBMS=XLSX REPLACE;
     SHEET="car"; 
RUN;

LIBNAME

• Add xlsx in the libname statement;

Libname exl xlsx "C:\Excel\car.xlsx";
data exl.cars;
set SASHELP.CARS ;
run;
libname exl clear;

SAS:Output SAS Results to Excel Pivot Table

Purpose:

• To create a pivot table from sas data sets. Apply specific configuration to the pivot table.

Download the tagset from SAS support website.

• “http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/tableeditor.tpl”

Save the tagset to local directory

• “C:\Users\xxxxxx\Documents\My SAS Files(32)\9.3\TableEditor\tableeditor.tpl”

Code Example:

filename temp  “C:\Users\xxxxxx\Documents\My SAS Files(32)\9.3\TableEditor\tableeditor.tpl”;
%include temp;
ods tagsets.Tableeditor file=”C:\Users\xxxxxx\Documents\My SAS Files(32)\9.3\AAPR\Excel\temp\temp.html”
options(BUTTON_TEXT=”Create PivotTable”
AUTO_EXCEL=”yes”
UPDATE_TARGET=”C:\\EXAMPLE.XLSX”
OPEN_EXCEL=”no”
PIVOT_SHEET_NAME= “Academic Program Report_UG”
PIVOTPAGE=”faculty”
PIVOTROW=”progname,category, level2, level3″
PIVOTCOL=”year”
PIVOTDATA=”value”
PIVOT_GRANDTOTAL=”no”
PIVOT_SUBTOTAL=”no”
PIVOTDATA_FMT=”#,###”
PIVOT_FORMAT=”medium14″
DELETE_SHEETS=”Sheet1, Sheet2, Sheet3, Table_1″
QUIT=”yes”
);

Proc print data = gr;
var faculty progname level2 level3 year value category;
run;
ods tagsets.tableeditor close;

Code Structure:

1. set up tableedit targetset directory
2. call ods statement
3. config pivot table in options
4. run proc print statement
5. close ods

Notes for Options

• Auto_Excel = “yes” (open Excel automatically)
• Update_target=”C:\\example.xlsx” ( need to create the excelfile example in the directory first. Notice C:\\  in the directory)
• Open_Excel= “no” (running excel at the backgroup)
• Pivotpage= “faculty” (identify field for report filter)
• Pivot_grandtotal=”no” (hide the grandtotal in the pivot table default view)
• Pivot_subtotal= “no” (hide the sub-total in the pivot table default view)
• Quit= “yes” (exit excel)

Problems Identified

• format in the option setting not working
• print procedure doesn’t take long (22 seconds) for 20,000 records table, but ods process take longer time to generate the pivot table.  There is no stop signal to show that the process has been completed.
• The excel file doesn’t seem to be updated until you open the file and the prompt would ask if you would like to save the change and if you click yes the updated the pivot table will show.

 

SAS output to date stamped Excel file

Sometimes people were asking for data and you just provided it to them. If it is an annual completed static data, you don’t have to do the date stamp to keep a record on when you retrieve the data from the system.  However, if it is an operational ongoing data with day-to-day changes and also the data is not officially published for the year,  you might want to keep a record of the date for reference.

The SAS code here basically provides a date format that you want to show on the Excel file name.  The format code itself is quite complicated, but you don’t have to remember it at all. Just copy and paste. There are many variations to it.  Then use macro functions %unquote and %sysfunc to get date and time as part of the file name.

proc format;
picture mydtfmt
low-high = ‘%Y_%0m_%0d_%0I%0M%p’ (datatype=datetime);
run;

PROC EXPORT DATA= Section
OUTFILE= “C:\Documents\My SAS Files(32)\9.3\Exce\section_%unquote(%sysfunc(datetime(),mydtfmt.)).xlsx”
DBMS=EXCEL REPLACE;
SHEET=”section”;
RUN;

Output:

In Windows Explorer,