topic Re: Ods Excelxp default output restriction in ODS and Base Reporting

Ods Excelxp default output restriction

sanjay1 — Thu, 18 May 2017 10:05:51 GMT

Hi SAS Experts,

I am using ods excelxp to output xml files and to wrap the text columns, and am able to do that. But by default ods excelxp is opening the xml files after exporting them, here I want to stop this default behaviour of ods excelxp, is there is any options to do this.

Actually there more than 100 files am creating using excelxp concept and it witll be difficult for me to close them each time.

Here is my code.

data a;
set sashelp.class;
run;

data a;
set a;
amount=age*weight;
run;

proc sql;
select count(distinct sex) into : c from a;
quit;

proc sql;
select distinct sex into:sex1-:sex%sysfunc(compress(&c)) from a;
quit;

%macro b;
%do i=1 %to &c;
ods tagsets.excelxp file="path\a_&&sex&i...xml" style=statistical options(absolute_column_width='10,5,5,5,5');
proc print data=a noobs;
var name sex age weight height;
var amount/style(data)={tagattr='format:"$"#,##0'};
where sex="&&sex&i.";
run;
ods tagsets.excelxp close;
%end;
%mend b;
%b;

Thanks & regards,

Sanjay.

Re: Ods Excelxp default output restriction

RW9 — Thu, 18 May 2017 10:52:28 GMT

Put before your code:

ods noresults;

And afterwards:

ods results;

This turns off the resulting output.

Just to add, why are you creating 100 odd files? Doesn't seem to be the most efficient use of disk space or reviewers time? Its simpler to code to output to a new sheet each time, cuts down numbers of files.

Re: Ods Excelxp default output restriction

sanjay1 — Thu, 18 May 2017 11:24:13 GMT

Hi RW9,

yeah you are right, actually am creating this for distinct users.

Thank you its working for me.