BookmarkSubscribeRSS Feed
Ravikumarkummari
Quartz | Level 8

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.

actual output.PNGdesired output.PNG

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

Reeza
Super User
No, but ODS EXCEL allows you to control that partially.
Vince_SAS
Rhodochrosite | Level 12

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1375 views
  • 1 like
  • 4 in conversation