topic Having problems with ODS Excel and dates and highlighting in ODS and Base Reporting

Having problems with ODS Excel and dates and highlighting

SASGeek — Tue, 09 Jul 2024 20:57:15 GMT

Hello,

I'm have a problem with no highlighting in my results. I get results as expected (and yes, there is a 31Aug2022 in my dates) but nothing highlights. My code is as follows:

%let asofdt = '31Aug2022'd;

%let newfile = Newfile_data.xlsx;

ods excel file = "&newfile."
options (Sheet_Name = 'Focus_Results');

proc report data = newvals nowd missing;

column date_asof

ATM_results;

define date_asof / "As of Date" f=weekdate17. width = 10;

define ATM_results / "ATM Results" f=comma10. width = 10;

compute date_asof;

if date_asof = &asofdt. then call define(_row_, 'style', 'style={background=yellow}');

endcomp;

run;

ods excel close;

Thank you

Re: Having problems with ODS Excel and dates and highlighting

A_Kh — Tue, 09 Jul 2024 21:12:50 GMT

I think you are missing DEFINE statement usage options (eg. DISPLAY); Do you have a clean log?
See the modification below and try it.

define date_asof / "As of Date" f=weekdate17. width = 10 display;
define ATM_results / "ATM Results" f=comma10. width = 10 display;

Re: Having problems with ODS Excel and dates and highlighting

ballardw — Tue, 09 Jul 2024 22:41:31 GMT

@A_Kh wrote:

I think you are missing DEFINE statement usage options (eg. DISPLAY); Do you have a clean log?
See the modification below and try it.
define date_asof / "As of Date" f=weekdate17. width = 10 display;
define ATM_results / "ATM Results" f=comma10. width = 10 display;

Bet his log has:

NOTE: Variable date_asof is uninitialized.

By default numeric variables, which include dates, would be SUMMED. Which is why with my small example data set (HINT HINT HINT @SASGeek ) of:

data work.newvals;
   input date_asof :date9. atm_results;
   format date_asof date9.;
datalines;
31Aug2022  123456
01Sep2022  34555
02Oct2022  11111
;

The result looked like the following. Please note the displayed date.

As of Date	ATM Results
Thu, Feb 1, 2148	169,122

Re: Having problems with ODS Excel and dates and highlighting

SASGeek — Wed, 10 Jul 2024 13:11:57 GMT

Thank you so much. Using the display worked.

To answer your question: I did have a clean log and the output looked fine, it's just that it didn't highlight.

Thank you!