topic Re: Excel date conversion in SAS Enterprise Guide

Excel date conversion

Ross12345 — Mon, 19 Oct 2020 14:26:50 GMT

I never have an easy time converting dates in this language.

I imported a set of dates from an excel sheet that follow the form "2016/02/23 18:24:00"

Once loaded they have the following properties:

Type: Char

Len: 27

I would like to convert them to a workable SAS date format (date9.). I tried a number of approaches without success - online resources aren't super helpful when it comes to SAS date formats.

Please advise

Re: Excel date conversion

PaigeMiller — Mon, 19 Oct 2020 14:39:17 GMT

data example;
    datetime_char="2016/02/23 18:24:00";
    datetime_want=input(datetime_char,anydtdtm.);
    date_want=datepart(datetime_want);
    format datetime_want datetime18. date_want date9.;
run;

Re: Excel date conversion

ballardw — Mon, 19 Oct 2020 14:46:11 GMT

@Ross12345 wrote:

I never have an easy time converting dates in this language.

I imported a set of dates from an excel sheet that follow the form "2016/02/23 18:24:00"

Once loaded they have the following properties:

Type: Char

Len: 27

I would like to convert them to a workable SAS date format (date9.). I tried a number of approaches without success - online resources aren't super helpful when it comes to SAS date formats.

Please advise

Part of the issue is that "dates" in Excel sometimes aren't. I've had to process files where half the dates were entered as character values. So even Excel couldn't treat them as dates, or more properly, datetime values.

It might help to share the approaches that did not work and exactly why they didn't.

BTW, this is an "online resource" and we get to answer this question in one form or another at least once a week.

Re: Excel date conversion

Reeza — Mon, 19 Oct 2020 15:33:14 GMT

Here's a great, but longer and in depth, reference for dates and times in SAS
https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/ta-p/424354

@Ross12345 wrote:

I never have an easy time converting dates in this language.

I imported a set of dates from an excel sheet that follow the form "2016/02/23 18:24:00"

Once loaded they have the following properties:

Type: Char

Len: 27

I would like to convert them to a workable SAS date format (date9.). I tried a number of approaches without success - online resources aren't super helpful when it comes to SAS date formats.

Please advise