topic Re: Incorrect sum of julian date in SAS Programming

Incorrect sum of julian date

Vish33 — Tue, 14 Apr 2015 08:18:39 GMT

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;

Re: Incorrect sum of julian date

SASKiwi — Tue, 14 Apr 2015 08:38:52 GMT

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?

Re: Incorrect sum of julian date

Kurt_Bremser — Tue, 14 Apr 2015 08:51:56 GMT

Why do you think you'd get 586625?

Re: Incorrect sum of julian date

Vish33 — Tue, 14 Apr 2015 10:24:43 GMT

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.

Re: Incorrect sum of julian date

Kurt_Bremser — Tue, 14 Apr 2015 10:37:20 GMT

How is the reconcile value computed originally?

Re: Incorrect sum of julian date

Vish33 — Tue, 14 Apr 2015 13:53:55 GMT

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

Re: Incorrect sum of julian date

SASKiwi — Tue, 14 Apr 2015 19:52:06 GMT

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.

Re: Incorrect sum of julian date

CTorres — Tue, 14 Apr 2015 20:48:04 GMT

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

26-Nov-73

;

run;

proc sql;

select sum(juldate) from chk;

quit;

Regards,

Re: Incorrect sum of julian date

Kurt_Bremser — Wed, 15 Apr 2015 05:33:59 GMT

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.

Re: Incorrect sum of julian date

Kurt_Bremser — Wed, 15 Apr 2015 05:35:33 GMT

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.

Re: Incorrect sum of julian date

jakarman — Wed, 15 Apr 2015 05:39:15 GMT

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?

Re: Incorrect sum of julian date

Kurt_Bremser — Wed, 15 Apr 2015 05:45:41 GMT

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

Re: Incorrect sum of julian date

Vish33 — Wed, 15 Apr 2015 06:41:41 GMT

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....

Re: Incorrect sum of julian date

SASKiwi — Wed, 15 Apr 2015 07:09:21 GMT

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

Re: Incorrect sum of julian date

Vish33 — Wed, 15 Apr 2015 09:06:09 GMT

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

Thank you ALL.

Re: Incorrect sum of julian date

Reeza — Wed, 15 Apr 2015 10:04:28 GMT

Doesn't @CTorres code generate the expected value?

Re: Incorrect sum of julian date

Vish33 — Wed, 15 Apr 2015 11:35:54 GMT

Ya..it's generated correct value...in this context

If i don't see for any data quality..this is correct. Actual the raw file itself wrong, so I have sent the file back to IT saying the field is wrongly calculated.

Re: Incorrect sum of julian date

Reeza — Wed, 15 Apr 2015 12:15:01 GMT

I'm going to assume the actual data values are then incorrect? i.e. the dates are not in NOV 1973 which is denoted as 73327 in numeric format/julian date.

Regardless, CTorres answered your original question correctly and this question should be marked answered IMO.

Re: Incorrect sum of julian date

Vish33 — Wed, 15 Apr 2015 13:13:52 GMT

Accepted..:)