Help using Base SAS procedures

Proc Report "compute before _page_" affects Autofilter

Reply
New Contributor
Posts: 3

Proc Report "compute before _page_" affects Autofilter

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

Super User
Super User
Posts: 7,942

Re: Proc Report affects Autofilter

Posted in reply to DoUKnowWhoIAm

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.

New Contributor
Posts: 3

Re: Proc Report affects Autofilter

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 !

Super User
Super User
Posts: 7,942

Re: Proc Report affects Autofilter

Posted in reply to DoUKnowWhoIAm

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:

<AutoFilter x:Range="R4C1:R432C15" xmlns="urn:schemas-microsoft-comSmiley Surprisedffice:excel"></AutoFilter><Table ssSmiley FrustratedtyleID="_body">

Note the range would be the one to modify.

SAS Super FREQ
Posts: 8,864

Re: Proc Report affects Autofilter

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

Ask a Question
Discussion stats
  • 4 replies
  • 368 views
  • 0 likes
  • 3 in conversation