How to convert a character datetime to a date MM/DD/YYY

[ScotchCat]

Hi - Need a bit of help -  I have a dataset that has 10,000+ rows, there's a variable that's a character datetime $19. format that needs to be converted to a true date format when exported to Excel formatted as MM/DD/YYYY. 

example of character datetime: 2017-07-21 00:00:00

Needs to be a date: 07/21/2017

I've tried a couple of things, however no success yet - Thanks in advance!

[ballardw]

---

@ScotchCat wrote:

Hi - Need a bit of help -  I have a dataset that has 10,000+ rows, there's a variable that's a character datetime $19. format that needs to be converted to a true date format when exported to Excel formatted as MM/DD/YYYY. 

example of character datetime: 2017-07-21 00:00:00

Needs to be a date: 07/21/2017

I've tried a couple of things, however no success yet - Thanks in advance!

---

It often helps to show what you tried that did not work so we can provide reasons why.

A very common mistake is trying to do the conversion of character to date (which is numeric) into the same variable. Once the variable is created SAS will not allow you to actually change the value.

You should be able to get a date value from the character you show as

datevar = input(datetime,yymmdd10.);

and then you will want a FORMAT to apply the desired appearance as

format datevar mmddyy10.;

Hint: Make sure that the initial reading of data into SAS has the desired properties. That will save much time later.

[ScotchCat]

I tried this and the Datetime character stays the same - no change:

Data Want;

set Have;

NewDate = input(Datetime,yymmdd10.);

format NewDate mmddyy10.;

[Shmuel]

When you export sas data to excel, a sas numeric date will be transferred to excel as excel numeric date

but you can't force excel - to display date in a specified format - by sas.

[ScotchCat]

Thanks Shmuel, however after running the suggested code the dataset in SAS didn't update to a date and remained a character. 

[AMSAS]

Here's a simple example, you'll need to adjust it for your situation
Basically I create a string variable (cDateTime), convert that to a SAS date time value (sasDateTime), extract the date part (sasDate) and finally print all the variables, both in their raw form and formatted.

Please refer to the documentation on SAS Formats and Informats

data want ;
	infile cards ;
	input cDateTime $19. ;
	sasDateTime=inputn(cDateTime,"datetime.") ;
	sasDate=datepart(sasDateTime) ;
	put cDateTime= sasDateTime= sasDateTime= datetime. sasDate= sasDate= date. ;

cards;
04APR2019 14:35
06APR2019 06:45
;
run ;

[ballardw]

---

@ScotchCat wrote:

I tried this and the Datetime character stays the same - no change:

Data Want;

set Have;

NewDate = input(Datetime,yymmdd10.);

format NewDate mmddyy10.;

---

I think that you need to provide some actual example of your HAVE data set.

This works just fine:

data example;
   x='2017-07-21 00:00:00';
   y= input(x,yymmdd10.);
   format y date9.;
run;

You code might not work if you have 1) leading spaces in your datetime variable, 2) your actual value is a SAS datetime value and you only think it is character or 3) some other headache with the actual values.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.