datepart

[woo]

Hello, i have "datetimevar" variable like below (it might missing ":" after "01JAN17", not quite sure) 

01JAN1753:00:00:00

how to get it right?  i want it with date9. I tried with date8. and date9. but didn't get it right, 

data test2;
set test1;
format new_date date9. ;
new_date=datepart(datetimevar);
run;

[Reeza]

Is your input variable a numeric with a datetime format? Or is it a character? I suspect it's a character which is a problem. You first need to convert that a datetime variable.

If so, try the following:
new_date = datepart(input(dateTimeVar, anydtdtm.));

[Astounding]

So you have two-digit years in your data?  That's always dangerous.  SAS will make an assumption about the right century, but it might be an incorrect guess.  At any rate:

data test;
   dt = "01JAN1753:00:00:00";
   format new_date date9.;
   new_date = input(dt, date7.);
run;

[Tom]

Not sure what you are asking.

SAS datasets have only two types of values. Floating point numbers and fixed length strings. Date, Datetime and Time values are stored as numbers.

Is the original variable a SAS number with the DATETIME format attached?  If so then it should have the value -6,532,185,600 representing the first second of the year 1753 and using DATEPART should yield a the number -75,604 which represents the first day of year 1753.

If the original variable is a string then you could use the DATETIME informat to convert it to a datetime value and then apply the DATEPART() function to convert to a date.  Or parse out the part before the : and use the DATE informat to convert it directly to a date.

date1 = datepart(input(charvar,datetime20.));
date2 = input(scan(charvar,1,':'),date9.);
format date1 date2 date9.;

[woo]

this value is coming from like oracle db and when i get into sas variable is showing with date type 

01JAN1753:00:00:00

[Reeza]

Check the variable type by using PROC CONTENTS.

proc contents data=test;
run;

If it a numeric with a datetime format your code is correct. 

What is NOT happening that you're expecting to happen?

---

@woo wrote:

this value is coming from like oracle db and when i get into sas variable is showing with date type 

01JAN1753:00:00:00

---

[woo]

when i do proc contents data=test; (for original oracle table) 

original value -> 01JAN1753:00:00:00

type=Num

len=8

format=datetime20.

informat=datetime20.

then if i do this, new_date come out with "." (period) 

data test2;

set test;

format new_date date9.;

new_date=put(orig_date, date7.);

run;

if i use substr function, new_date come out -> "26FEB1781"

data test3;
set test;
format new_date date9.;
new_date=substr(orig_date,1,7);
run;

[PaigeMiller]

How about this:

data test2;
    set test;
    format orig_date datetime9.;
run;

[Reeza]

---

@woo wrote:

when i do proc contents data=test; (for original oracle table) 

original value -> 01JAN1753:00:00:00

type=Num

len=8

format=datetime20.

informat=datetime20.

then if i do this, new_date come out with "." (period) 

data test2;

set test;

format new_date date9.;

new_date=put(orig_date, date7.);

run;

if i use substr function, new_date come out -> "26FEB1781"

data test3;
set test;
format new_date date9.;
new_date=substr(orig_date,1,7);
run;

---

Your original code was correct. Post the log and indicate why you think it's incorrect. If there are warnings in the log it's likely because you may have missing values/dates and when you try to use DATEPART on a missing value that will generate a warning in the log. 

[woo]

My original code,

orig_date -> 01JAN1753:00:00:00

data test2;
set test;
format new_date datetime9.;
new_date=datepart(orig_date);
run;

output

31DEC1959

[Reeza · Accepted solution]

What is the correct version of that date, January 1 1753 or January 1, 2017? If 2017, then the 53 is weird because where did that come from. If it is 1753 using datepart seems fine to me. 

data have;
date = '01JAN1753:00:00:00'dt;
date_want = datepart(date);
date_want_correct = input(substr(put(date, datetime20. -l), 1, 9), date9.);
format date_want: date9.;
run;

proc print;run;

What DB are you reading this from? Excel's the only way I've seen this issue with getting the wrong dates.

[Tom]

---

@woo wrote:

My original code,

orig_date -> 01JAN1753:00:00:00

data test2;
set test;
format new_date datetime9.;
new_date=datepart(orig_date);
run;

output

31DEC1959

---

Again that makes perfect sense.

You took a number in seconds and converted it to a number in days and then told SAS to display it as if it was still a number in seconds.  Since the original number was negative (before 1960) the resulting negative number of days when interpreted as seconds is some time in the last day in 1959.

[PaigeMiller]

---

@woo wrote:

My original code,

orig_date -> 01JAN1753:00:00:00

data test2;
set test;
format new_date datetime9.;
new_date=datepart(orig_date);
run;

output

31DEC1959

---

Answer already provided in message #8 of this thread.

[Tom]

So you have a numeric variable with datetime values.

then if i do this, new_date come out with "." (period) 

data test2;

set test;

format new_date date9.;

new_date=put(orig_date, date7.);

run;

Which makes perfect sense.  You told SAS that NEW_DATE was a number because the first reference was attaching a numeric format to it.  You then tried to store a string like '01JAN1753' into it.  So the result was a missing value.  SAS will try to autoconvert strings into numbers but it does not know how to convert that string into a number.  It will use the normal F informat when it tries to autoconvert strings into numbers.  Instead you wanted:

new_date=datepart(orig_date);

if i use substr function, new_date come out -> "26FEB1781"

data test3;
set test;
format new_date date9.;
new_date=substr(orig_date,1,7);
run;

So now you are asking SAS to convert the number ORIG_DATE into a character string so that you can try to use it with the character function SUBSTR().  SAS will use the format BEST12. when trying to autoconvert numbers into strings.  So since you variable has the value that means it has the number -6,532,185,600.  The value will be right aligned so there will be one leading space. So the first 7 characters will be ' -65321'.  Then since you attached the DATE9 format it displays it as 26FEB1781 since that is the data that is that many days before the start of 1960.

Please explain what you are actually trying to do?

[Astounding]

You would have done much better if you had posted the PROC CONTENTS information originally. Omitting that led to different suggestions based on different assumptions. That makes life harder for you to decide who is right and who is wrong. Just a word to the wise for the next time you have a question.