DATE calculation

[Xinhui]

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. 

[ChrisNZ]

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

[PGStats · Accepted solution]

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;

[Xinhui]

Thanks for your reply. it still not work.

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

[Xinhui]

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

[Xinhui]

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

[PGStats]

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.

[Xinhui]

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 

[PGStats]

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

[Xinhui]

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

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

[PGStats]

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

[Xinhui]

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;

[PGStats]

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;

.

[Xinhui]

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

[Tom]

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