false "high-value" for a date in visual analytics 7.1

[mariusg]

hi,

we use the "31DEC9999" to identify the actual valid dataset.

Today we build a simple report with a drop-down list to filter the uploaded table on demand.

But the displayed high-value is the 29DEC9999. Because i am really sure that the table contains records with 31DEC9999, i opened SASStudio (same server) and searched for the 31DEC9999.... and there it is (screen2).

So maybe there is a bug or something really strange in our environment.

Perhaps someone can help and run this simple dataset and load the data into memory to check if it works correct.

thank you

marius

libname otto '/app/.....';

data otto.datetest;

format mydate date9.;

format mydateNr 8.;

do mydate="01JAN2015"d to "31DEC9999"d by 1;

mydateNr = mydate;

output;

end;

run;

proc sql;

select * from test.datetest where mydate="31DEC9999"d;

quit;

[tlt]

Hi,

Very strange screenshot indeed.

Took your code and run it with SAS Studio (University Edition)

and get for |mydate, mydateNr] = [31DEC9999, 2936547]

In SAS, a date is represented by the number of days from January 1rst, 1960.

It looks like in your environment, there is a shift of 2 days.

You could try this code:

data _null_;

  d=0; put d= d= date9. d=yymmdds10.;

  d=1; put d= d= date9. d=yymmdds10.;

  d=2936545; put d= d= date9. d=yymmdds10.;

  d=2936546; put d= d= date9. d=yymmdds10.;

  d=2936547; put d= d= date9. d=yymmdds10.;

run;

On my desktop, log shows :

d=0 d=01JAN1960 d=1960/01/01

d=1 d=02JAN1960 d=1960/01/02

d=2936545 d=29DEC9999 d=9999/12/29

d=2936546 d=30DEC9999 d=9999/12/30

d=2936547 d=31DEC9999 d=9999/12/31

HTH

[mariusg]

Hi,

yes that`s really strange.

I guess there is a bug in va, when someone wants to display a high date like 31DEC9999.

Because the 0 = 01JAN1960 works and other values  <9999 too.

Perhaps someone can help and run the dataset and load the data into memory to check if this is a global issue.

thank you

marius

[MichelleHomes]

Hi Marius,

I was curious to see this myself and did the following testing.

1) I ran your code in SAS Enterprise Guide and got the dates as you did.

2) I loaded the data into SAS Visual Analytics 6.4 and the date value for 31DEC9999 is the same as you experienced. Perhaps this is due to leap years over time and for some reason this is calculated differently in SAS VA. Why? :smileyconfused:

3) I was curious to know at what point is the difference between the dates less than one so I exported the data from SAS Visual Analytics and imported it into SAS Enterprise Guide and calculated the lag to see where it was less than 1 and found the following...

data test;

   set work.export;

   if lag<1;

   format mydate date9.;

run;

And the result table, test contains the value:

Interesting observation ! As the date 31DEC9999 is traditionally used to indicate a valid/open transaction when working with slowly changing dimension data, it is crucial to ensure the SAS date 31DEC9999 is the same number as when used on the SAS Workspace Server. I agree there seems to be an issue with large dates in SAS Visual Analytics. Please report your findings to SAS Technical Support.

Kind Regards,

Michelle

[jakarman]

What could be?

- There is some statement they have build something new for SAS-VA not using all the old code.

- Whithin JAVA there as whole approach on dates. DateFormat (Java Platform SE 6) (format and calculations on the epoch)

  These are possible different to JAVA version and machines.

Gettting the bad feeling there is some difference introduced  only getting into that problems using those high 9999 years leap-year or whatever

Definitely something to bother TS for.