topic Re: DATE calculation in SAS Programming

DATE calculation

Xinhui — Thu, 21 Nov 2019 01:00:38 GMT

Hi there

Plase see the attached data file, I'm attempt to calculate the days between varible 'date' and 'edate', I used following codes:

data final3;
set final2;
FORMAT DATE MMDDYY10.;
FORMAT EDATE MMDDYY10.;
DD=DATE- EDATE;
run;

However, all of the DD is missing in the file, it seems it not calculate correctly.

the code works in other project.

I don't know why it not work in this case.

I suspect that maybe the format of the "date" and "edate" has some problem.

Thanks in advance.

Re: DATE calculation

ChrisNZ — Thu, 21 Nov 2019 01:50:28 GMT

> I suspect that maybe the format of the "date" and "edate" has some problem.

No. Formats do not affect such calculations.

Show us the log, and a proc contents of table FINAL2.

Re: DATE calculation

PGStats — Thu, 21 Nov 2019 04:23:23 GMT

I suspect your dates are strings (and this should generate messages in the log when you run the code). Anyway,

/* Try replacing */

FORMAT DATE MMDDYY10.;
FORMAT EDATE MMDDYY10.;
DD=DATE- EDATE;

/* With */

dt = input(date, mmddyy10.);
edt = input(edate, mmddyy10.);
dd = dt - edt;

/* Or */

dt = input(date, yymmdd10.);
edt = input(edate, yymmdd10.);
dd = dt - edt;

Re: DATE calculation

Xinhui — Thu, 21 Nov 2019 04:45:52 GMT

Thanks for your reply. it still not work.

log information shows "ERROR: There was a problem with the format so BEST was used"

Re: DATE calculation

Xinhui — Thu, 21 Nov 2019 04:58:31 GMT

the sencond one works!!!! Thank you so much!!!

Re: DATE calculation

Xinhui — Wed, 27 Nov 2019 19:13:01 GMT

Sorry it was worked, but I rerun the code, it failed again, why this happened?

Re: DATE calculation

PGStats — Wed, 27 Nov 2019 19:43:46 GMT

We can't tell unless you post the SAS log showing the problem. If you get different results (errors) with the same code, then the change must be in the data.

Re: DATE calculation

Xinhui — Wed, 27 Nov 2019 19:44:55 GMT

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).

622:12 623:13

NOTE: Invalid argument to function INPUT at line 623 column 7.

Company_Name=ARCHER-DANIELS-MIDLAND CO TICKER=ADM EDATE=20140204 Date=20100107 Price=31.23 dt=22189 edt=. dd=.

_ERROR_=1 _N_=4

NOTE: Invalid argument to function INPUT at line 623 column 7.

Company_Name=ARCHER-DANIELS-MIDLAND CO TICKER=ADM EDATE=20150203 Date=20100108 Price=30.84 dt=22189 edt=. dd=.

_ERROR_=1 _N_=5

this is the log information

Re: DATE calculation

PGStats — Wed, 27 Nov 2019 19:54:19 GMT

Looks like DATE and EDATE are not character strings anymore, but numbers.. You could do:

dt = input(put(date,best8.), yymmdd10.);
edt = input(put(edate, best8.), yymmdd10.);

Re: DATE calculation

Xinhui — Wed, 27 Nov 2019 20:01:35 GMT

works now. Thank you so much!!!!!!!

But do you know why this happened? I use the same file, why old code not work?

Re: DATE calculation

PGStats — Wed, 27 Nov 2019 20:03:47 GMT

With SAS, the same code normally yields the same result. But if your data resides in Excel... anything can happen.

Re: DATE calculation

Xinhui — Wed, 27 Nov 2019 20:37:59 GMT

Now I'm trying to merge two files by "date", but I can not, it seem the same problem, the format of "date" is not recognized.

Here is the log information:

NOTE: Table has been opened in browse mode.
ERROR: There was a problem with the format so BEST. was used.
ERROR: There was a problem with the format so BEST. was used.

the code I used is

DATA master;
MERGE T2 (in=a)
sp2 (in=b);
by date;
if A;
run;

Re: DATE calculation

Xinhui — Wed, 27 Nov 2019 20:55:44 GMT

the format of "date" in file T2 is BEST12.

the format of "date" in file SP2 is YYMMDDN8.

Re: DATE calculation

PGStats — Wed, 27 Nov 2019 21:07:14 GMT

These error messages are not related to the merge operation. They likely occur when you try to display the dataset with the SAS dataset viewer (a very poor component of the SAS environment). Try displaying your data with proc print

proc print data=myData(obs=30); run;

Re: DATE calculation

Xinhui — Wed, 27 Nov 2019 21:49:11 GMT

in the print table the ‘date' showed as *******

Re: DATE calculation

Tom — Wed, 27 Nov 2019 22:16:18 GMT

SAS will print **** when the value is too large/small for the format attached.

If you attach a date format (like DATE, MMDDYY, DDMMYY, YYMMDD etc) to a variable that has values like 20,191,127 then you are asking it to display a date that is probably in the year 53,320. But the DATE format can only display 4 digits for the year part of a date.

1131  data _null_;
1132    date= 20191127 ;
1133    year_est = floor(date/365.25) - 1960;
1134    put date= comma20. year_est= comma10.;
1135  run;

date=20,191,127 year_est=53,320