DATA Step, Macro, Functions and more

Exporting from SAS to Excel

Reply
Contributor
Posts: 60

Exporting from SAS to Excel

 

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

SAS Super FREQ
Posts: 8,868

Re: Exporting from SAS to Excel

[ Edited ]
Posted in reply to CamRutherford

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:

ods_excel_bygroup.png

Contributor
Posts: 60

Re: Exporting from SAS to Excel

Posted in reply to CamRutherford

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; 




Respected Advisor
Posts: 4,173

Re: Exporting from SAS to Excel

[ Edited ]
Posted in reply to CamRutherford

@CamRutherford

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;

 

Contributor
Posts: 60

Re: Exporting from SAS to Excel

@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

Respected Advisor
Posts: 4,173

Re: Exporting from SAS to Excel

Posted in reply to CamRutherford

Hi @CamRutherford

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;

 

Ask a Question
Discussion stats
  • 5 replies
  • 136 views
  • 1 like
  • 3 in conversation