topic Help Converting Numbers to Dates! PLEASE HELP! in SAS Programming

Help Converting Numbers to Dates! PLEASE HELP!

rangarat — Tue, 05 Feb 2019 15:15:07 GMT

I'm converting numeric dates to SAS Dates using suggestions of other users but I keep getting the following notes:

NOTE: Invalid argument to function INPUT at line 80 column 16.

NOTE: Mathematical operations could not be performed at the following places. The results of the
operations have been set to missing values.
Each place is given by: (Number of times) at (Line):(Column).
6 at 80:16.

I used SAS code suggested by other users to convert numeric dates (e.g., 19900723) in SAS, but I've had no luck.

The SAS code is:

DATA INPHAASE.FORMATDATES;
SET INPHAASE.INITIALDB;
ABSDATE = INPUT(PUT(ABSTRACTION_DATE,8.),YYMMDD8.);
FORMAT ABSDATE YYMMDD8.;
RUN;

I'd appreciate any help.

Re: Help Converting Numbers to Dates! PLEASE HELP!

PaigeMiller — Tue, 05 Feb 2019 15:22:39 GMT

Is 19900723 really a number, or is it a character string?

If it is really a number, turn it into a character string first (use the CATS function, but there are many ways to do this)

data a;
    abstraction_date=19900723;
    z=input(cats(abstraction_date),yymmdd8.);
    format z date7.;
run;

If it is a character string, the leave out the CATS function and its parentheses.

Re: Help Converting Numbers to Dates! PLEASE HELP!

Kurt_Bremser — Tue, 05 Feb 2019 15:24:47 GMT

First of all, stop shouting. At us and at the SAS system.

Your algorithm works, as can be seen here:

data have;
input date_orig;
cards;
19900723
;
run;

data want;
set have;
date_correct = input(put(date_orig,8.),yymmdd8.);
format date_correct yymmddd10.;
run;

proc print data=want noobs;
run;

Result:

  date_          date_
  orig         correct

19900723    1990-07-23

So the problem has to be with your data. Maybe you have missing values, or invalid values for SAS dates.

Filter formatdates for missing values of absdate, and inspect abstraction_date there.

Re: Help Converting Numbers to Dates! PLEASE HELP!

ballardw — Tue, 05 Feb 2019 16:02:09 GMT

@rangarat wrote:

I'm converting numeric dates to SAS Dates using suggestions of other users but I keep getting the following notes:

NOTE: Invalid argument to function INPUT at line 80 column 16.

NOTE: Mathematical operations could not be performed at the following places. The results of the
operations have been set to missing values.
Each place is given by: (Number of times) at (Line):(Column).
6 at 80:16.

I used SAS code suggested by other users to convert numeric dates (e.g., 19900723) in SAS, but I've had no luck.

The SAS code is:

DATA INPHAASE.FORMATDATES;
SET INPHAASE.INITIALDB;
ABSDATE = INPUT(PUT(ABSTRACTION_DATE,8.),YYMMDD8.);
FORMAT ABSDATE YYMMDD8.;
RUN;

I'd appreciate any help.

Some instruction on reading that error message: Note the "number of times" highlighted in blue refers to the 6 in the second line highlighted. If your data had more than 6 observations then this tells you the operation didn't fail for the rest. So the clue is to examine the input values of your variable abstraction_date.

You can extract those observations with :

data junk;
   set inphaase.formatdates;
   where missing(absdate);
run;

I will suspect that when you examine the values you will find the numeric representation to of abstraction_date to be: 1) different length than 8 digits, or 2) value outside the range of 01 to 12 in the 5 and 6th digits or 3) value outside of valid number of days for a month or 4) much less likely year < 1581.

Common date errors for some sources are Feb 29 when not actually a leap year or day 31 in months with only 30 days.

I have received data that had a 67th day of November once.

Re: Help Converting Numbers to Dates! PLEASE HELP!

ErikLund_Jensen — Tue, 05 Feb 2019 17:38:26 GMT

Hi Rangarat

Your inner function PUT(ABSTRACTION_DATE,8.) converts numeric abstraction_date to a string. The CAT function is a better choice, as it takes both numeric and string numbers and converts to string without errors or warnings regardless of input type.

The problem comes if the string is empty or contains an invalid date. But that can be fixed with a format modifier, that turns invalid or missing dates into missing values without errors or warnings. You you could drop records with missing values later if you want.

I have 3 examples, note the ?? as format modifier in 2 and 3:

123  data _null_;
124      ABSTRACTION_DATE = 20180533;
125      ABSDATE = input(cat(ABSTRACTION_DATE),yymmdd8.);
126      put ABSDATE=;
127  run;

NOTE: Invalid argument to function INPUT at line 125 column 15.
ABSDATE=.
ABSTRACTION_DATE=20180533 ABSDATE=. _ERROR_=1 _N_=1
NOTE: Mathematical operations could not be performed at the following places. The results of the
      operations have been set to missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1 at 125:15
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


128
129  data _null_;
130      ABSTRACTION_DATE = 20180530;
131      ABSDATE = input(cat(ABSTRACTION_DATE),??yymmdd8.);
132      put ABSDATE=;
133  run;

ABSDATE=21334
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


134
135  data _null_;
136      ABSTRACTION_DATE = .;
137      ABSDATE = input(cat(ABSTRACTION_DATE),??yymmdd8.);
138      put ABSDATE=;
139  run;

ABSDATE=.

Re: Help Converting Numbers to Dates! PLEASE HELP!

Tom — Tue, 05 Feb 2019 17:53:05 GMT

How many observations in your dataset? The error message says it only happened 6 times. If that is 6 out 10,000 observations then it is not such a big deal. If it is 6 out of 6 observations than your data is not like what your code is assuming. Somewhere in between and you might want to see what values are giving it trouble and program around those.

Re: Help Converting Numbers to Dates! PLEASE HELP!

Cynthia_sas — Tue, 05 Feb 2019 17:58:16 GMT

Hi:
I'm not sure what the problem is. In my calendar, there isn't any day 33, so THIS value:
ABSTRACTION_DATE = 20180533;

Should and does result in an invalid argument message....because there was not May 33 in 2018. And, it's a NOTE that you need to see because it is telling you that some of your data is invalid -- worth checking out and trying to correct.

Cynthia

Re: Help Converting Numbers to Dates! PLEASE HELP!

ErikLund_Jensen — Tue, 05 Feb 2019 19:11:52 GMT

@Cynthia_sas

We run a data warehouse with about 4000 jobs in our daily batch. Errors that results in return codes are captured by the scheduling environment, but nobody is actually looking through all log files, so notes like "converted to numeric" og "missing values were generated" are not detected.

An automated log scan finds jobs with issues that should be corrected, and the common cause is sloppy coding or changes in data, that should be handled. But if the problem is invalid data, and the data source is outside your control, e.g. national data bases, especially old legacy systems with dates entered as free user input maybe 20 years back. you have to live with the problem.

An invalid date will always result in a missing value, so the result will be the same, but if the error cannot be corrected, and you don't want the same job popping up on your alert list every day as "noise", it can be suppressed by ysing the format modifier. I do not recommend it as a general coding practise, but in some cases it can be very useful.