topic converting character date to numeric and then to date format in SAS Programming

converting character date to numeric and then to date format

Dhana18 — Mon, 23 Sep 2019 14:22:55 GMT

Good morning,
i have a data set with a date in character format. I used this below code to change the character date into numeric date format.

data SURRGWED.CDC_Clinic_data ;
set  WEDSSUAT.Surrg_ForCDC_Clinic_v;
visdt=input(visdate,best10.);
drop visdate;
rename visdt=visdate;
run;

then used this code below to change the numeric date into date format.
 
data SURRGWED.CDC_Clinic_data_A ;
set SURRGWED.CDC_Clinic_data ;
format visdate mmddyy10.;
run;
 but it is nit working. the log says :
 data SURRGWED.CDC_Clinic_data_A ;
90   set SURRGWED.CDC_Clinic_data ;
91   format visdate mmddyy10.;
92   run;

NOTE: There were 6066 observations read from the data set SURRGWED.CDC_CLINIC_DATA.
NOTE: The data set SURRGWED.CDC_CLINIC_DATA_A has 6066 observations and 67 variables.
NOTE: DATA statement used (Total process time):
      real time           0.70 seconds
      cpu time            0.07 seconds

The out put is 
Visdate
.
.
.
.
.
.

Please help!

Re: converting character date to numeric and then to date format

ballardw — Mon, 23 Sep 2019 14:38:07 GMT

@Dhana18 wrote:

Good morning,
i have a data set with a date in character format. I used this below code to change the character date into numeric date format.

data SURRGWED.CDC_Clinic_data ;
set  WEDSSUAT.Surrg_ForCDC_Clinic_v;
visdt=input(visdate,best10.);
drop visdate;
rename visdt=visdate;
run;

then used this code below to change the numeric date into date format.
 
data SURRGWED.CDC_Clinic_data_A ;
set SURRGWED.CDC_Clinic_data ;
format visdate mmddyy10.;
run;
 but it is nit working. the log says :
 data SURRGWED.CDC_Clinic_data_A ;
90   set SURRGWED.CDC_Clinic_data ;
91   format visdate mmddyy10.;
92   run;

NOTE: There were 6066 observations read from the data set SURRGWED.CDC_CLINIC_DATA.
NOTE: The data set SURRGWED.CDC_CLINIC_DATA_A has 6066 observations and 67 variables.
NOTE: DATA statement used (Total process time):
      real time           0.70 seconds
      cpu time            0.07 seconds

The out put is 
Visdate
.
.
.
.
.
.

Please help!

Please show what your original visdate looked like.

Likely you want to use one of the DATE informats to read the data not BEST.

Since you are using a BEST10 I suspect the issue is that you have attempted to apply a date format to a value outside the range of actual date variables. The numeric value 6589335 corresponds to 31Dec20000 (yes 2 followed by 5 zeroes). And that is only a 7 digit value. That is the largest date value SAS currently supports.

Example of reading some common date looking values.

data junk;
   x1= '15012019';
   y1=input(x1,ddmmyy8.);
   x2= '01152019';
   y2=input(x2,mmddyy8.);
   x3= '20190115';
   y3=input(x3,yymmdd8.);
   format y: date9.;
run;

Re: converting character date to numeric and then to date format

Dhana18 — Mon, 23 Sep 2019 14:54:09 GMT

Here is the original date:
Visdate
0217/01/03
1/17/2017
1/6/2017
1/30/2017
1/23/2017
2/13/2017

Re: converting character date to numeric and then to date format

ballardw — Mon, 23 Sep 2019 16:53:57 GMT

@Dhana18 wrote:
Here is the original date:
Visdate
0217/01/03
1/17/2017
1/6/2017
1/30/2017
1/23/2017
2/13/2017

That would explain the missing values. None of those can be imported with Best or F formats.

If that first one is actually supposed to be 2017/01/03 then you have mixed formats for your date.

You might be able to use the ANYDTDTE informat:

visdt=input(visdate,anydtdte10.);

format visdt mmddyy10.;

The question is if you are sure that with dates like 1/6/2017 where either the first or second value could be the month. You national language setting will determine the interpretation of such possibly ambiguous dates. At least you appear to have 4 digit years, otherwise unspecified date layouts can be messy (01/02/03 could be 1) Jan 2, 2003, 2) Feb 1, 2003 or 3) Feb 3 2001 )

If it weren't for your first value I would suspect that the MMDDYY10. informat would work.