topic Re: Convert char to Date in SAS Programming

Convert char to Date

Q1983 — Thu, 07 Jan 2021 17:20:41 GMT

data have;
date1='44204.70833333334';
date2=input(date1,mmddyy10.);
run;

I recreated this for demonstration purposes. I have a file that I import. The date
imports as a character(date1). I need to convert it to a mmddyy10. style date.
I attempted to do that in date2 however I get a blank value. Is there a way to convert this
character field to a date field

Re: Convert char to Date

ballardw — Thu, 07 Jan 2021 17:28:50 GMT

@Q1983 wrote:

data have;
date1='44204.70833333334';
date2=input(date1,mmddyy10.);
run;

I recreated this for demonstration purposes. I have a file that I import. The date
imports as a character(date1). I need to convert it to a mmddyy10. style date.
I attempted to do that in date2 however I get a blank value. Is there a way to convert this
character field to a date field

I will bet a large stack of $$$ that value at some time was an Excel DATE and Time value.

There is no way that any of the SAS MMDDYY, YYMMDD, DDMMYY or similar INFORMATS could read that value. Where is the Month, Day or Year in "44204".

Create a numeric value:

numval = floor (input(date1,5.) );

The floor function is to remove the time portion, the decimals.

Then search the forum for any of the plethora of Excel to SAS date conversions.

If you actually want to convert this to a date time value then extract the decimals as a number and multiple by 24*60*60 to convert the fraction of a day the decimals represent into seconds. Then use the DHMS function with the date and seconds to create a datetime SAS value: DT = dhms(date,0,0,seconds);

Re: Convert char to Date

Tom — Thu, 07 Jan 2021 17:39:32 GMT

Looks to me like you have imported an Excel spreadsheet where one of the column had some datetime values and some character strings. So SAS had to create the variable as character. When it does that the datetime values are stored as strings that represent the number Excel uses to store the value. First convert the value to a number using INPUT() then adjust to value by the difference in basis dates used for counting days.

date2=input(date1,32.)+'30DEC1899'd ;

If you need the time component you will need to convert the value from days to seconds. In Excel the 0.70833333334 means what percent of 24 hours.

date3=dhms(date2,0,0,0);

Then attach the display format you want to use for these two new variables.

format date2 date9. date3 datetime19. ;

Results:

214   data have;
215     date1='44204.70833333334';
216     date2=input(date1,32.)+'30DEC1899'd ;
217     date3=dhms(date2,0,0,0);
218     format date2 date9. date3 datetime19. ;
219     put (_all_) (=/);
220     put (date2 date3) (= comma20. /);
221   run;


date1=44204.70833333334
date2=08JAN2021
date3=08JAN2021:17:00:00

date2=22,289
date3=1,925,744,400