How to -> Convert Datetime format to Numeric&Character Format

[turcay]

Hello everyone,

I try to convert datetime format to numeric or character format without using Format option. I wrote a sample code. My purpose is that see the Numeric variable as "1903" being numeric and the Character variable as "01JAN1960" being character in WANT data set. I do not want to use Format option because when I try to use Format option the results come as asterisk(*****). I don't understand why I can't see the RevertNumeric variable as "1903" in Want data set. Does somebody can help me, please ?

Data Have;
Numeric=1903;
Character="01JAN1960";
CharacterFormatted=Input(Character,DATE9.);
Format Numeric Datetime20. CharacterFormatted Datetime20.;
RUN;
Data Want;
Set Have;
RevertNumeric=Input(Numeric,Datetime20.);
/*Format Numeric 8.;*/
RUN;

Thank you

[ballardw]

What are you expecting the value of RevertNumeric to be?

The error you are getting, but not mentioned, is that when you execute this line:

RevertNumeric=Input(Numeric,Datetime20.)

Since Numeric is a number to use the INPUT function SAS turns it into a character using an internal algorithm that results in trying to read "1903" not the formatted value you expect.

To force the use of a specific format you need to say which one.

try this instead.

Data Want;
   Set Have;
   RevertNumeric=Input(put(Numeric,datetime20.),Datetime20.);
RUN;

[ChrisNZ]

So many things to say. Let's start here:

1- A series of stars in your formatted value means that your variable is too short. Lengthen it.

2- Formats are the best way to manipulate dates etc into strings and vice versa, so try to make it work.

3- CharacterFormatted is a SAS date in your code. It contains number 0 (meaning day # zero), which is SAS's representation for the 1st of Jan 1960. So you should not format it as a datetime. Value 0 used with a datetime format tells SAS to use this as second # zero rather than day # zero.

I hope this makes sense. Look up date, datetime, and time storage in SAS if it doesn't.

[turcay]

Hello @ChrisNZ, You made the right point for me, thanks. Firstly, when I write datetime it brings "01JAN1960 12:00:00 AM" but when I write date it brings "18MAR1965" I don't what is the differences between them. I couldn't exactly get this part -> "Value 0 used with a datetime format tells SAS to use this as second # zero rather than day # zero." Secondly, I tried to convert datetime20. format to date9. format but it came as a series of stars in my database but when I try to perform in my sample data set , it works properly without asteriks. I couldn't get the Length issue ? Could you help me, please ?

[Tom · Accepted solution]

SAS stores everything as either a fixed length string or a floating point number. To enable working with dates and times SAS uses three different conventions for how to interpret (ie FORMAT) the information in those floating point numbers. For TIME values it is the number of seconds since midnight. For dates it is the number of days since 01JAN1960.  For datetime values it is the number of seconds since 01JAN1960.

If you want to convert a DATE value to a DATETIME value you can use the DHMS() function.  dtvar=DHMS(datevar,0,0,0);

If you want to convert a DATETIME value to a DATE value you can use the DATEPART() function.  datevar=DATEPART(dtvar);

If you want to read as stirng in DATE9 format ("01JAN2015") using an INFORMAT that generates a DATETIME value then you get the number of seconds instead of the number of days.  So you need to use a datetime FORMAT with it.  If you try to format a DATETIME value (a really big number) as a DATE you will get a date that potentially needs more than 4 digits to store the year.  You could use the DTDATE9. format to display just the date part of the datetime value.

data _null_;
  string='18MAR1965';
  date1 = input(string,date9.);
  date2 = '18MAR1965'd ;
  datetime1 = input(string,anydtdtm.);
  datetime2 = '18MAR1965:00:00'dt ;
  put (_all_) (=/);
  put date1 = date9. / datetime1 = datetime20. / datetime1 = dtdate9. ;
run;

string=18MAR1965
date1=1903
date2=1903
datetime1=164419200
datetime2=164419200
date1=18MAR1965
datetime1=18MAR1965:00:00:00
datetime1=18MAR1965

[ballardw]

---

@turcay wrote:
Hello @ChrisNZ, You made the right point for me, thanks. Firstly, when I write datetime it brings "01JAN1960 12:00:00 AM" but when I write date it brings "18MAR1965" I don't what is the differences between them. I couldn't exactly get this part -> "Value 0 used with a datetime format tells SAS to use this as second # zero rather than day # zero." Secondly, I tried to convert datetime20. format to date9. format but it came as a series of stars in my database but when I try to perform in my sample data set , it works properly without asteriks. I couldn't get the Length issue ? Could you help me, please ?

---

Here is a brief example of displaying the same numeric value with different formats. The values are the same but how SAS uses the Format to display changes.

data junk;
   x=1903;
   y=x;
   z=x;
   q=z;
run;

proc print data=junk noobs;
format x best. y mmddyy10. z datetime20. q julday.;
run;

Result:
 x               y                       z                             q

1903    03/18/1965      01JAN1960:00:31:43    77

[Tom]

You need to provide more information as your example doesn't make much sense.

What date do you think 1903 represents?

data _null_;
  number=1903;
  put number / number time8. / number date9. / number datetime20. ;
run;

1903   
0:31:43 
18MAR1965   
01JAN1960:00:31:43

[ChrisNZ]

Regarding stars:

data _null_; 
  X=12345;
  put X 2. / X 5.; 
run;

prints

**

12345

because the first format is too short for the number to display.

[Kurt_Bremser]

You have a fundamental misconception about date and datetime values.

READ THE DOCUMENTATION ABOUT THAT!!

Datetime values are stored as a number containing the seconds from 01jan1960:00:00:00, allowing for fractions of a second.

Date values are stored as a number containing the days from 01jan1960, fractions are ignored when displayed.

Therefore you cannot convert a date to datetime and vice versa by simply changing the format.

Use the proper functions to convert these values from one type to the other (datepart(),dhms()).

[turcay]

Thank you all of you. I will accept Tom's answer as a solution. But first I would like to ask one more question. I wrote following code and it worked but when I added Order by statement the date variable is coming as dot(".").  I tried to handle it by using some methods but I couldn't succeed .I also checked this document -> http://support.sas.com/documentation/cdl/en/etsug/60372/HTML/default/viewer.htm#etsug_tsdata_sect010...  .Could you help me, please Here is my code ->

PROC SQL;
CREATE TABLE Have AS
SELECT F1.ID
,Datepart(F2.Date) FORMAT=DDMMYY10. AS NewDate
FROM Oralib.Fraud1 F1
Left Join Oralib.Fraud2 F2
ON F1.PolicyId=F2.PolicyId And F1.RID=F2.RID
/*ORDER BY NewDate*/
/*ORDER BY Date*/
/*ORDER BY Datepart(F2.Date)*/;
QUIT;

PROC SORT DATA=Have;
BY NewDate;
RUN;