Incorrect sum of julian date

[Vish33]

Hi ALL,

i have a variable with julian date format,. when i tried to calculate sum of the field it's not giving the actual result.ANy help would be appriciated.

Here is the sample code: Actual result should be 586625, but am getting 40609.

data chk;
informat dat date9.;
format dat julian5. ;
input dat ;
cards;
23-Nov-73
26-Nov-73
23-Nov-73
23-Nov-73
23-Nov-73
23-Nov-73
26-Nov-73
26-Nov-73
;
run;

proc sql;
select sum(dat) from chk;
quit;

[SASKiwi]

Internally SAS stores dates as the number of days since 1 Jan 1960, so the sum producing 40609 is quite correct.

If you want the number 40609 represented as a Julian date:

proc sql;

select sum(dat) format = julian5.

from chk;

quit;

Adding up a series of dates doesn't make a lot of sense to me. What do you wish to get as a result?

[Kurt_Bremser]

Why do you think you'd get 586625?

[Vish33]

I have a raw file which is having header, data and trailer records. In trailer i have this value as roconcile value.So  need to get this value to match with the reconcile field.

[Kurt_Bremser]

How is the reconcile value computed originally?

[Vish33]

It's .txt sample file. I am not sure on this..sample has been provided by IT team....they might calculate in excel.

[SASKiwi]

If they are doing it in Excel then Excel holds dates as the number of days since 1 Jan 1900. I still don't see how you get 586625.

[Kurt_Bremser]

So you have to work with undocumented "data", but need to make a test of correctness? Doesn't that sound quite stupid to you?

Return the whole mess to sender.

[Kurt_Bremser]

And yes, it can't be Excel, because that renders a completely different sum. Or the "test" data are incomplete, which signifies incompetence, gross, major at the place of origin.

[Vish33]

Yes..i know this is not at all correct validation...our badluck ...we are working with this junk data...i have sent this back to the sender....

[CTorres · Accepted solution]

Try this:

data chk;

informat dat date9.;

format dat julian5. ;

input dat ;

juldate=input(put(dat,julian5.),5.);

cards;

23-Nov-73

26-Nov-73

23-Nov-73

23-Nov-73

23-Nov-73

23-Nov-73

26-Nov-73

26-Nov-73

;

run;

proc sql;

select sum(juldate) from chk;

quit;

Regards,

[jakarman]

SAS is using date numbers as an number offset (point of zero) since 1 jan 1960. Excel is doing that for positive numbers only with a point of zero as a 1 jan 1900. How does color 9 smell?

[Kurt_Bremser]

We're quickly approaching a new piece of Vogon poetry.

[SASKiwi]

I was hoping the answer might be 42. LOL! Apologies if you don't know about the Hitchhikers Guide to the Galaxy.

[Vish33]

I am closing this thread as i have raised it as data defect.

Thank you ALL.