topic Re: ods tagsets.excelxp user define range in ODS and Base Reporting

ods tagsets.excelxp user define range

Ravikumarkummari — Tue, 02 Oct 2018 13:50:38 GMT

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.

Re: ods tagsets.excelxp user define range

RW9 — Tue, 02 Oct 2018 14:01:27 GMT

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?

Re: ods tagsets.excelxp user define range

Reeza — Tue, 02 Oct 2018 14:50:36 GMT

No, but ODS EXCEL allows you to control that partially.

Re: ods tagsets.excelxp user define range

Vince_SAS — Tue, 02 Oct 2018 21:22:28 GMT

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