05-18-2017 12:18 PM
I am looking to have visibility of some code that will export a data set from SAS directly into excel whilst creating multiple uniuq tabs based on values in a specific column.
For example, I have a data set with Name, Country, Age, Addr1, Addr2 and I would like it to export to excel and create a tab for each country with the relavant data.
05-18-2017 12:25 PM - edited 05-18-2017 12:34 PM
You can use
1) PROC EXPORT
2) LIBNAME engine
3) ODS EXCEL (if you have SAS 9.4)
4) ODS TAGSETS.EXCELXP (if you have SAS 9.0+)
You are the only person who can tell us what you want. You did not show any code that you tried and you did not show any desired output and you did not provide test data, so that makes it hard to help.
If you are going to use Methods #1 or #2, you will get "plain" output that looks like you typed it into Excel. There are no colors, fonts, styles, etc applied to this output.
On the other hand, with methods #3 and #4, you do have the ability to automatically name the sheets and get styles applied in the sheets.
There have been a lot of previous postings about how to get SAS output in Excel. My suggestion is that you search in the forum for other postings.
Here's an example of #3 -- if you want something other than this, search for the other methods in the Forums:
05-19-2017 05:04 AM
So I have multiple datasets in SAS that I want to put on individual tabs in Excel and get the file sent to my email address. Currently I have the below code...
/*CIRC BY CAMPAIGN SINCE MAR 2016*/ PROC SQL; CREATE TABLE CAMPAIGN_CIRCULATION AS SELECT DISTINCT (CAMPAIGN_NAME), DATEPART(PLANNED_DROP_DATE) FORMAT DDMMYY10. AS PLANNED_DROP_DATE , COUNT(*) AS CIRCULATION, PRODUCT, CELL_MANAGER, LINE_OF_BUSINESS, MARKETING_TEAM, CHANNEL, CELL_NAME FROM MART.DN_ATR_METADATA_CIRC_1 WHERE PLANNED_DROP_DATE >= TODAY()-14 GROUP BY CAMPAIGN_NAME, PLANNED_DROP_DATE, CELL_MANAGER, LINE_OF_BUSINESS, MARKETING_TEAM, CHANNEL, CELL_NAME ;QUIT; /*ALL OF 2016 & 17 TRANSACTIONS*/ PROC SQL; CREATE TABLE TRANSACTIONS AS SELECT DISTINCT(PRODUCT_GROUP_1),PRODUCT_NAME, DATEPART(DATE_TRANS) FORMAT DDMMYY10. AS DATE_TRANS , COUNT(*) AS TOTAL FROM MART.DN_ATR_TRANS_2016_2017_NEW WHERE DATE_TRANS >= TODAY()-14 GROUP BY PRODUCT_GROUP_1,PRODUCT_NAME, DATE_TRANS ORDER BY PRODUCT_GROUP_1 ;QUIT; /*UNIQ CAMPAIGNS*/ PROC SQL; CREATE TABLE CAMPAIGNS_UNIQ AS SELECT DISTINCT CAMPAIGN_NAME, PLANNED_DROP_DATE FROM CAMPAIGN_CIRCULATION GROUP BY CAMPAIGN_NAME, PLANNED_DROP_DATE ;QUIT; /*preparing the files for email distribution*/ %let user = tul0cba; %let dataset=CAMPAIGN_CIRCULATION; %let dataset1=/user/&user/CAMPAIGN_CIRCULATION.txt; %let dataseta=TRANSACTIONS; %let dataset2=/user/&user/TRANSACTIONS.txt; %let datasetb=CAMPAIGNS_UNIQ; %let dataset3=/user/&user/CAMPAIGNS_UNIQ.txt; /*dataset 1*/ Data &dataset; set CAMPAIGN_CIRCULATION; run; proc export data=&dataset outfile="&dataset1" dbms=tab replace; run; /*dataset 2*/ Data &dataseta; set TRANSACTIONS; run; proc export data=&dataseta outfile="&dataset2" dbms=tab replace; run; /*dataset 3*/ Data &datasetb; set CAMPAIGNS_UNIQ; run; proc export data=&datasetb outfile="&dataset3" dbms=tab replace; run; ***update email recipients here***; filename mymail email To=("email@example.com" ) subject="SAS TREND REPORT EXPORT" attach=("&dataset1" "&dataset2" "&dataset3"); data _null_; file mymail; put 'Hi there,'; put "Please see the attached files"; put"Thanks,"; put"CWR automated message"; run;
05-19-2017 05:57 AM - edited 05-19-2017 05:59 AM
The code writing to Excel tabs could look like:
/*preparing the files for email distribution*/ %let path = c:\temp; ods _all_ close; ods excel file="&path\mult_sheet_by.xlsx" style=htmlblue ; %let table=sashelp.class; ods excel options(sheet_name="%scan(&table,-1)"); proc print data=&table noobs; run; %let table=sashelp.air; ods excel options(sheet_name="%scan(&table,-1)"); proc print data=&table noobs; run; %let table=sashelp.airline; ods excel options(sheet_name="%scan(&table,-1)"); proc print data=&table noobs; run; ods excel close; ods listing;
05-19-2017 09:46 AM
@Patrick Thankyou for this.
However, I don't understand what it is doing. Also I am accessing SAS through Citrix which is on a seperate server which is why I need to email the file to myself before saving it somewhere as I don't have a path location or anything, just an email address I can send the file to.
05-19-2017 05:49 PM
You've asked for code which exports SAS tables into sheets in a single Excel Workbook but the code you've posted creates a tab delimited text file per source SAS table.
The code I've posted creates this single Excel you've asked for. It's a replacement for your code between below lines of comment in your code.
/*preparing the files for email distribution*/ <here the code creating the Excel> ***update email recipients here***;
"I don't have a path location"
In the code you've posted there are paths as well. Your code executes on a SAS server and there is certainly a file system there (not the same than what you get in your local environment though) where you can write data to.
You've been using /user/&user so should this be an existing path then use this one.
%let path = /user/&user;