BookmarkSubscribeRSS Feed
DoUKnowWhoIAm
Calcite | Level 5

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

DoUKnowWhoIAm
Calcite | Level 5

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 !

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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-com:office:excel"></AutoFilter><Table ss:StyleID="_body">

Note the range would be the one to modify.

Cynthia_sas
SAS Super FREQ

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1107 views
  • 0 likes
  • 3 in conversation