Hello,
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.
Thanks,
Cam
Hi:
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.
cynthia
Here's an example of #3 -- if you want something other than this, search for the other methods in the Forums:
Hi,
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=("mr.cam.ruthers@abc.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;
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;
@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.
Thanks,
Cam
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***;
....
attach=("&path/mult_sheet_by.xlsx")
....
"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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.