Date format changing

[yichentian226]

Hi,

I have a simple question on changing data from this format "2020-10-05T20:49:52.850Z" to "10/5/2020" . I used format date ddmmyy10. but no luck on that... any suggestion is appreciated.

[ballardw · Accepted solution]

The value you show is a DATETIME, not a DATE. SAS supplies a few formats that will display datetime values with only the date portion but none of the ones I see result in a ddmmyy format.

You have two choices. One is to extract the date portion of the variable and apply the format to that:

dateonly = datepart(datetimevariable);

format dateonly ddmmyy10.;

Or look into the Proc Format documentation for the Picture statement for details on creating a custom format to display datetime values directly as ddmmyy10.; It is not difficult but you get into some issues depending on your knowledge base related to format maintenance and availability.

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates and related.

[yichentian226]

Thank you!

I am trying to go with the first choice you offered. However sas is giving me error saying datetimevariable has been defined as both character and numeric. In this case how can I change it to character? ( I believe we should change it to character so that the format dateonly ddmmyy10. can be used for next step? )

[ballardw]

---

@yichentian226 wrote:

Thank you!

I am trying to go with the first choice you offered. However sas is giving me error saying datetimevariable has been defined as both character and numeric. In this case how can I change it to character? ( I believe we should change it to character so that the format dateonly ddmmyy10. can be used for next step? )

---

You will only get that sort of message when dealing with two data sets and a common variable name. So ONE of the data sets was created with a character variable and the other numeric. You lose a lot of flexibility creating character variables from dates, times or datetime variables. I would tend to go the other way and make a numeric version.

Suggestion at this time: go back to how the data is brought into SAS that is typically when such things happen an generally comes from relying on Proc Import or an import wizard. Since every file examined separately by these then you have the possibility of different variable types, lengths and variable names. Setting a standard process will prevent such things.

This is relatively common problem and a well know dance to rename the "bad" version of the variable and create a "correct" version by inputing the bad version to the correct.

A small example:

data old;
   x="2020-10-05T20:49:52.850Z" ;

run;

data new;
  x ='15Jan2020:12:15:20'dt;
  format x datetime.;
run;

data combine;
   set old (rename=(x=oldx))
       new
   ;
   if not missing(oldx) then x=input(oldx,anydtdtm.);
   dateonly = datepart(x);
   format dateonly ddmmyy10.;
run;

There is a specific informat to read datetime data in that form but I'm  too lazy to look it up right now.

The main part is the rename and treatment.

Or take a separate data step to modify your existing data. Hint: make sure you have a new data set name otherwise logic errors might corrupt your data .

[yichentian226]

Below is what I wrote but it's still giving me the "defined as both character and numeric" error; 

DATA Report;
published_time= put(published, z8.);
DROP published;
RENAME published_time=published;
SET Report;
RUN;

I am wondering if it's because "published"'s length is long? We have 24 here. Look forward to your thoughts thanks!

[ballardw]

Show the code from the LOG that generates the error message. Include all of the data step or procedure code along with all messages. That way we at least know which data set.

---

@yichentian226 wrote:

Below is what I wrote but it's still giving me the "defined as both character and numeric" error; 

DATA Report;
published_time= put(published, z8.);
DROP published;
RENAME published_time=published;
SET Report;
RUN;

I am wondering if it's because "published"'s length is long? We have 24 here. Look forward to your thoughts thanks!

---

Please look very closely your code above and compare the code I suggested.The options I provided are dataset options and occur before the data is actually used. That is quite different from your code.

You are using

published_time= put(published, z8.);

is before the set statement. Order of assignment or data manipulation statements in relation to SET  or INPUT statements that bring data into the set is important.

So there are NO values created.But the numeric variable Published is created, with missing value, and published_time as an 8 character variable.

You can verify this behavior by running this code:

data junk;
   published_time= put(published, z8.); 
run;

Which will generate a message about the variable published is not initialized (NO values). The output set will have two variables but they are both missing. (Print the data if you want).

If a variable "published" appears in your data on the SET statement and is a Character variable that causes the conflict.

Which can duplicate your error with this code:

data pub;
   published ='Sometext';
run;
data junk2;
   published_time= put(published, z8.); 
   set pub;
run;

Plus you may have completely corrupted your starting data and need to go back to get an older version.

The code construct where you have the same name as the result data set and on the Set statement is legal. However any code in this manner completely rewrites the data set. Which often causes all sorts of problems.