topic Re: Proc Report affects Autofilter in SAS Procedures

Proc Report "compute before _page_" affects Autofilter

DoUKnowWhoIAm — Wed, 05 Mar 2014 09:55:15 GMT

Hi all,

I have recently started at a new client.

Alot of old code and they are macro crazy.

I have a proc report with a compute before _page_ block within ODS output (excelxp tagset).

It seems this compute block has the effect of shifting my autofilter to the first row of the data section and off the heading row.

proc report data=work.dummy nowindows split='*'

style(column)={background=#ffcc33 foreground=black font_weight=bold};

compute before _page_ / style=line;

line "&session_fullName_oBlanks.";

line ' ';

endcomp;

columns btrName eve_status mAnzahlAbsolut BE_Geplant_abs WDB_Geplant_abs mAnzahlProzent BE_Erwartung;

define btrName / '';

define eve_status / '';

define mAnzahlAbsolut / '' style(Column)=[just=right tagattr='formula:subtotal(9,R[2]C[0]:R[50000]C[0]) format:#,##0'];

define BE_Geplant_abs / '' style(Column)=[just=right tagattr='formula:subtotal(9,R[2]C[0]:R[50000]C[0]) format:#,##0'];

define WDB_Geplant_abs / '' style(Column)=[just=right tagattr='formula:subtotal(9,R[2]C[0]:R[50000]C[0]) format:#,##0'];

define mAnzahlProzent / '' style(Column)=[just=right tagattr='formula:subtotal(9,R[2]C[0]:R[50000]C[0]) format:#,##0'];

define BE_Erwartung / '' style(Column)=[just=right tagattr='formula:subtotal(9,R[2]C[0]:R[50000]C[0]) format:#,##0'];

run;

Any thoughts on what to do with this issue ?

Regards

Re: Proc Report affects Autofilter

RW9 — Wed, 05 Mar 2014 11:21:12 GMT

Probably Excel being "helpful". I just tried this code:

ods tagsets.excelxp file="S:\Temp\Rob\x.xls" options (autofilter="yes");

proc report data=sashelp.cars nowindows split='*';

compute before _page_ ;

line "aaaaa";

line ' ';

endcomp;

run;

ods tagsets.excelxp close;

And it put the autofilter on the data, so maybe try removing bits of the program and re-running each time to see which works. Could be the style=line statement or somewhere else in the style which is telling Excel which is row headings.

Re: Proc Report affects Autofilter

DoUKnowWhoIAm — Wed, 05 Mar 2014 15:26:24 GMT

Thx, Excel sometimes is a little too helpful ...

I have tried by removing parts of the Program. It doesn't seem to work.

I'll keep on trying !

Thanks for the help !

Re: Proc Report affects Autofilter

RW9 — Wed, 05 Mar 2014 15:31:59 GMT

If all else fails you could use a hammer and go with reading the generated file - its a text xml file - and updating the line:

Note the range would be the one to modify.

Re: Proc Report affects Autofilter

Cynthia_sas — Wed, 05 Mar 2014 16:29:20 GMT

Or, the other alternative (and in my mind, the better alternative) is to open a track with Tech Support. Tech Support can look at the code (all the code) and the data and if necessary, involve a developer who knows TAGSETS.EXCELXP.

cynthia