BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
turcay
Lapis Lazuli | Level 10

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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


 

View solution in original post

9 REPLIES 9
ballardw
Super User

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
Tourmaline | Level 20

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
Lapis Lazuli | Level 10
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
Super User Tom
Super User

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
Super User

@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
Super User Tom
Super User

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
Tourmaline | Level 20

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
Super User

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
Lapis Lazuli | Level 10

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 33476 views
  • 8 likes
  • 5 in conversation