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;
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,
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?
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.
How is the reconcile value computed originally?
It's .txt sample file. I am not sure on this..sample has been provided by IT team....they might calculate in excel.
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.
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.
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.
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....
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,
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?
We're quickly approaching a new piece of Vogon poetry.
I was hoping the answer might be 42. LOL! Apologies if you don't know about the Hitchhikers Guide to the Galaxy.
I am closing this thread as i have raised it as data defect.
Thank you ALL.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: