BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Vish33
Lapis Lazuli | Level 10

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;

1 ACCEPTED SOLUTION

Accepted Solutions
CTorres
Quartz | Level 8

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

18 REPLIES 18
SASKiwi
PROC Star

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?

Vish33
Lapis Lazuli | Level 10

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.

Vish33
Lapis Lazuli | Level 10

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

SASKiwi
PROC Star

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
Super User

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
Super User

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
Lapis Lazuli | Level 10

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
Quartz | Level 8

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
Barite | Level 11

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 --<-----
SASKiwi
PROC Star

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

Vish33
Lapis Lazuli | Level 10

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

Thank you ALL.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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