DATA Step, Macro, Functions and more

Incorrect sum of julian date

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 117
Accepted Solution

Incorrect sum of julian date

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;


Accepted Solutions
Solution
‎04-14-2015 04:48 PM
Regular Contributor
Posts: 180

Re: Incorrect sum of julian date

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,

View solution in original post


All Replies
Super User
Posts: 3,250

Re: Incorrect sum of julian date

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?

Super User
Posts: 7,758

Re: Incorrect sum of julian date

Why do you think you'd get 586625?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 117

Re: Incorrect sum of julian date

Posted in reply to KurtBremser

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.

Super User
Posts: 7,758

Re: Incorrect sum of julian date

How is the reconcile value computed originally?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 117

Re: Incorrect sum of julian date

Posted in reply to KurtBremser

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

Super User
Posts: 3,250

Re: Incorrect sum of julian date

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.

Super User
Posts: 7,758

Re: Incorrect sum of julian date

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,758

Re: Incorrect sum of julian date

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 117

Re: Incorrect sum of julian date

Posted in reply to KurtBremser

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

Solution
‎04-14-2015 04:48 PM
Regular Contributor
Posts: 180

Re: Incorrect sum of julian date

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,

Trusted Advisor
Posts: 3,211

Re: Incorrect sum of julian date

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?

---->-- ja karman --<-----
Super User
Posts: 7,758

Re: Incorrect sum of julian date

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 3,250

Re: Incorrect sum of julian date

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

Frequent Contributor
Posts: 117

Re: Incorrect sum of julian date

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

Thank you ALL.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 18 replies
  • 523 views
  • 1 like
  • 6 in conversation