Date calculation

[Xinhui]

Hi there 

 I'm attempt to calculate the days between varible 'date' and 'edate',  I used following codes: 

data final3;
set final2;
dt = input(date, yymmdd10.);
edt = input(edate, yymmdd10.);
dd = dt - edt;
run;

It works before, but for somereason, it not work anymore. 

the log information shows that 

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

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. 

[Krueger]

Are you trying to get a date or a character result? INPUT() is converting both date and edate (assuming they were numeric to begin with) to character. If your trying to subtract a date from another date however they would need to be numeric. Try something like this:

dd = date-edate;
format dd yymmdd10.;   

Run a proc contents and look at what your formats are on date and edate. I'm guessing one might be character and you need to convert it to numeric first.

[Xinhui]

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

Initially, I used this code, it not works, then someone suggested me to use

data final3;
set final2;
dt = input(date, yymmdd10.);
edt = input(edate, yymmdd10.);
dd = dt - edt;
run;

it worked, but I rerun the whole project again, it not works again. 

I though there are some format problems with "date""edate", now is best12 format.

[Krueger]

Sorry formats wouldn't have any effect. What are the column attribute types for these 2 dates. Character or numeric?

[Xinhui]

numeric

[Krueger]

It's likely that you have null or missing values in your original dataset then. You'll need to either address these null/missing values or not calculate those?

[Xinhui]

There are no missing value there, that's why I am confuse now. 

Is there have any way that I can send the data file to you? 

[unison]

Is this what you're looking for?

data have;
  date="02/12/2018";
  edate="02/24/2018";
run;

data want;
set have;
format date_num edate_num date9.;
date_num = input(compress(date,"/"),mmddyy8.);
edate_num = input(compress(edate,"/"),mmddyy8.);
date_diff = edate_num-date_num;
run;

-unison

[Reeza · Accepted solution]

data final3;
set final2;
dt = input(put(date, 8. -l), yymmdd10.);
edt = input(put(edate, 8. -l), yymmdd10.);
dd = dt - edt;
run;

 

Try that. 

I think you have a date that's a number but not a SAS date, 

ie 2019/11/20 is shown as the number 20191120 not a SAS date. So first convert it to a character and then re-read it as a SAS date. There are other ways to do this but this is one way.

---

@Xinhui wrote:

Hi there 

 I'm attempt to calculate the days between varible 'date' and 'edate',  I used following codes: 

data final3;
set final2;
dt = input(date, yymmdd10.);
edt = input(edate, yymmdd10.);
dd = dt - edt;
run;

It works before, but for somereason, it not work anymore. 

the log information shows that 

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

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. 

---