proc export to different sheets

Reply
Contributor
Posts: 62

proc export to different sheets

[ Edited ]

I have 10 different stores and their sales.Now using proc export i want to put the data of each store in same excel file but in different 

sheets.How can i do this while using macros so my code is automated.

 

data test;

input store sales;

cards;

1 100

1 200

2 2000

2 2300

3 3000

.

.

.

10 100000

;

so i want store 1 all sales in sheet1 and store 2 sales in sheet 2 and so on?

Super User
Super User
Posts: 7,971

Re: proc import and export

Use sheet_interval options:

ods tagsets.excelxp file="abc.xlsx" options=(sheet_interval=bygroup);
proc report data=have;
  by store;
run;
ods tagsets.excelxp close;

Not tested obviously, but something similar to that. 

Contributor
Posts: 62

Re: proc import and export

so if i want to get sales by store ,country and any other parameter will it work?
data t;
input store sales cntry region;
cards;
1 100 US south
1 100 US north
1 1000 India south
1 2000 India north
;
now i want sales of each store of each cntry and of each region on new sheet.
Like store 1 US south region sales on one sheet and store 1 India south region on another?




Super User
Super User
Posts: 7,971

Re: proc import and export

Yes, try it:

data t;
  input store sales cntry $ region $;
  byg=catx("_",put(store,1.),put(sales,4.),cntry);
cards;
1 100 US south
1 100 US north
1 1000 India south
1 2000 India north
;
run;

proc sort data=t;
  by byg;
run;

ods tagsets.excelxp file="S:\temp\rob\test.xml" style=analysis options(sheet_interval='bygroup');
proc print data=t;
  by byg;
run;
ods tagsets.excelxp close;
Contributor
Posts: 62

Re: proc import and export

N if i have to import 10 different sheets to data sets then can i automate it?

Instead of writing 15 times proc import,can i use something similar to ods tagsets.excelxp?

Super User
Super User
Posts: 7,971

Re: proc import and export

This is a different question to the one you asked before and should go in a new topic.  For your question now, it depends on what version of SAS you are using and what the file type is.  If you have 9.4 and your file is an actual Excel XLSX, not a XML document with XLSX extension - i.e. the files you generate with ExcelXP tagset are not native Excel files! - then you can libname directyl to the XLSX file and it will automatcially create datasets:

http://www2.sas.com/proceedings/sugi31/024-31.pdf

 

However, if your not on that, or your file is not Excel, then it is more complicated.  I would suggest opening the the file in Excel, and saving each sheet to CSV, then writing a datastep import program to read the individual CSV files in, this has most control, proc import is a guessing procedure, and Excel is not structurally locked down, so both combined is not a good idea.  If you have a lot of sheets, then a simple macro in VBA can loop over sheets and save as CSV.

Contributor
Posts: 62

Re: proc import and export

can we use any procedure like proc print,proc freq or only proc report here?

Super User
Super User
Posts: 7,971

Re: proc import and export

For what purpose, to export the data?  Yes, any output procedure, print, report freq etc can be directed to the ods destination.

Super User
Posts: 10,035

Re: proc export to different sheets

[ Edited ]
data have;
input store sales cntry $ region $;
cards;
1 100 US south
1 100 US north
1 1000 India south
1 2000 India north
;
run;
proc sort data=have;by 	store cntry region;run;
option nobyline;
ods tagsets.excelxp file='c:\temp\tresto.xml' options(sheet_name="Store#byval1 #byval2 #byval3");
proc report data=have nowd;
by store cntry region;
define sales /display; run; ods tagsets.excelxp close;
Ask a Question
Discussion stats
  • 8 replies
  • 603 views
  • 0 likes
  • 3 in conversation