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!
@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.
I tried this and the Datetime character stays the same - no change:
Data Want;
set Have;
NewDate = input(Datetime,yymmdd10.);
format NewDate mmddyy10.;
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.
Thanks Shmuel, however after running the suggested code the dataset in SAS didn't update to a date and remained a character.
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 ;
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.