topic Re: Date calculation in SAS Programming

Date calculation

Xinhui — Wed, 27 Nov 2019 19:25:47 GMT

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.

Re: Date calculation

Krueger — Wed, 27 Nov 2019 19:32:55 GMT

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.

Re: Date calculation

Xinhui — Wed, 27 Nov 2019 19:43:19 GMT

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.

Re: Date calculation

Krueger — Wed, 27 Nov 2019 19:48:00 GMT

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

Re: Date calculation

Xinhui — Wed, 27 Nov 2019 19:49:25 GMT

numeric

Re: Date calculation

Krueger — Wed, 27 Nov 2019 19:51:30 GMT

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?

Re: Date calculation

unison — Wed, 27 Nov 2019 19:56:30 GMT

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

Re: Date calculation

Xinhui — Wed, 27 Nov 2019 19:56:34 GMT

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?

Re: Date calculation

Reeza — Wed, 27 Nov 2019 20:00:39 GMT

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.