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,105

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: 6,936

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

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: 6,936

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

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,105

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: 6,936

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: 6,936

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

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,

Valued Guide
Posts: 3,208

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: 6,936

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,105

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.

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

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