BookmarkSubscribeRSS Feed
CamRutherford
Fluorite | Level 6

 

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

5 REPLIES 5
Cynthia_sas
SAS Super FREQ

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

CamRutherford
Fluorite | Level 6

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; 




Patrick
Opal | Level 21

@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;

 

CamRutherford
Fluorite | Level 6

@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

Patrick
Opal | Level 21

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 767 views
  • 1 like
  • 3 in conversation