Hi all,
Is there any user define range option in ODS TAGSETS.EXCELXP.
Below set of code is submitted.
ODS TAGSETS.EXCELXP FILE="D:\Report.xls" STYLE=mystyle options(sheet_interval='none' sheet_name='CLASS' frozen_headers='yes'); OPTIONS MISSING=''; proc report data=class nowindows style(header)=[fontweight=bold BACKGROUND=BIGB FOREGROUND=black fontsize=8pt font=(calibri, 8pt) ] style(report)=[fontweight=bold BACKGROUND=BIGB FOREGROUND=black fontsize=8pt font=(calibri, 8pt) ] ; columns name sex age height weight sum_height_weight; define name/'student/ name/stansys' center style={just=c fontsize=8pt font=(calibri, 8pt) vjust=m}; define sex/order style={just=c fontsize=8pt font=(calibri, 8pt) vjust=m}; define age/ display 'age' style={just=c fontsize=8pt font=(calibri, 8pt) vjust=m}; define sum_height_weight/'heigt-weight' computed style={just=c fontsize=8pt font=(calibri, 8pt) vjust=m}; define height /style={just=c fontsize=8pt font=(calibri, 8pt) vjust=m}; define weight/ style={just=c fontsize=8pt font=(calibri, 8pt) vjust=m}; /*sub total*/ break after sex/summarize; compute after sex; sex=catx('-',sex,'total'); endcomp; /*grand total*/ rbreak after/summarize; compute after; sex='Grand Total'; endcomp; compute sum_height_weight; sum_height_weight=sum(height.sum,weight.sum); endcomp; run; ods tagsets.excelxp close;
The default output is started in the excle sheets from (A1-Range), But i want to change starting from (B1-Range).
Any help would be greatly appreciated.
Ods tagsets.excelxp creates a plain text file which has tags which allow Excel to read it, and parse it into Excel. Its pretty basic. You could have another column, and set the label=" ", i.e. creating an empty column, but thats about it. Why do you want a column before the data? Doesn't that just make it harder to read into other software?
Try something like this:
options missing='';
ods _all_ close;
ods Excel file='C:\temp\temp.xlsx'
options(sheet_interval='none'
sheet_name='CLASS'
frozen_headers='yes'
start_at='2,1'
flow='tables');
proc report data=sashelp.class nowindows
style(header)=[fontweight=bold background=bigb foreground=black fontsize=8pt font=(calibri, 8pt) ]
style(report)=[fontweight=bold background=bigb foreground=black fontsize=8pt font=(calibri, 8pt) ] ;
columns name sex age height weight sum_height_weight;
define name/'student/ name/stansys' center
style={just=c fontsize=8pt font=(calibri, 8pt) vjust=m};
define sex/order style={just=c fontsize=8pt font=(calibri, 8pt) vjust=m};
define age/ display 'age' style={just=c fontsize=8pt font=(calibri, 8pt) vjust=m};
define sum_height_weight/'heigt-weight' computed
style={just=c fontsize=8pt font=(calibri, 8pt) vjust=m};
define height /style={just=c fontsize=8pt font=(calibri, 8pt) vjust=m};
define weight/ style={just=c fontsize=8pt font=(calibri, 8pt) vjust=m};
* Subtotal;
break after sex/summarize;
compute after sex;
sex=catx('-',sex,'total');
endcomp;
*Grand total;
rbreak after/summarize;
compute after;
sex='Grand Total';
endcomp;
compute sum_height_weight;
sum_height_weight=sum(height.sum,weight.sum);
endcomp;
run; quit;
ods Excel close;
Vince DelGobbo
SAS R&D
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.