03-01-2016 08:05 AM - last edited on 03-01-2016 08:44 AM by LinusH
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.
input store sales;
so i want store 1 all sales in sheet1 and store 2 sales in sheet 2 and so on?
03-01-2016 08:15 AM
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.
03-01-2016 08:42 AM
03-01-2016 09:20 AM
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;
03-02-2016 04:16 AM
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:
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.
03-01-2016 10:00 PM - edited 03-01-2016 10:02 PM
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;