topic Re: datetime20. format in New SAS User

datetime20. format

rajeshm — Fri, 11 Sep 2020 16:59:26 GMT

/* MMDD1,2,3 are datetime20. format in tab dataset   02AUG2020:00:00:00*/
proc sql ;
create table Deposits_DetailXX as 
select 
MMDD1,
MMDD2 format=dtdate9.,
datepart(MMDD3) format=date9.
from tab;
quit;

tab dataset opened

MMDD1                       MMDD2         _TEMA001
02AUG2020:00:00:00  02AUG2020    02AUG2020

exported as xlsx

MMDD1	                         MMDD2	        _TEMA001
8/2/2020  12:00:00 AM	1911945600	2-Aug-20

1. where is MMDD3 varible? is it renamed to _tema001 ? why and how?

2. i used somewhere this format dtdate9. got expected result. but i am not getting how to use now..

3. date9. can't applied on datetime varible ,so datepart is calculated and then applied date9. and it is working fine.

Re: datetime20. format

Kurt_Bremser — Fri, 11 Sep 2020 17:09:32 GMT

When you create a new column in SQL through a calculation or function, it does not have a name, so you either give it one, or the SQL procedure will assign one automatically. Do this:

datepart(MMDD3) format=date9. as MMDD3

Re: datetime20. format

Reeza — Fri, 11 Sep 2020 17:22:01 GMT

@rajeshm wrote:

1. where is MMDD3 varible? is it renamed to _tema001 ? why and how?

You never named the variable so SAS assigned a default name. When you apply a transformation to a variable it's no longer the old variable, you explicitly need to give it a name. This is a SQL standard across the board.

2. i used somewhere this format dtdate9. got expected result. but i am not getting how to use now..

Formats with a DT in front are typically for DATETIME values, DT means DateTime format for a DATETIME variable. Using DATEPART converts a variable from a DATETIME to a DATE so a DATETIME variable is no longer valid.

3. date9. can't applied on datetime varible ,so datepart is calculated and then applied date9. and it is working fine.

The variable is a DATE variable now, so you can use a DATE format, not a DATETIME format.

Here's a great, but longer and in depth, reference for dates and times in SAS
https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/ta-p/424354

@rajeshm wrote:
/* MMDD1,2,3 are datetime20. format in tab dataset   02AUG2020:00:00:00*/
proc sql ;
create table Deposits_DetailXX as 
select 
MMDD1,
MMDD2 format=dtdate9.,
datepart(MMDD3) format=date9.
from tab;
quit;
tab dataset opened
MMDD1                       MMDD2         _TEMA001
02AUG2020:00:00:00  02AUG2020    02AUG2020
exported as xlsx
MMDD1	                         MMDD2	        _TEMA001
8/2/2020  12:00:00 AM	1911945600	2-Aug-20
1. where is MMDD3 varible? is it renamed to _tema001 ? why and how?

2. i used somewhere this format dtdate9. got expected result. but i am not getting how to use now..

3. date9. can't applied on datetime varible ,so datepart is calculated and then applied date9. and it is working fine.

Re: datetime20. format

rajeshm — Fri, 11 Sep 2020 17:39:40 GMT

mmdd2 is datetime20. informat

1. I would like to get the date from that variable, so used dtdtate9. to read date from datetime20...

dataset field value showing correct(dateformat). but while exporting xlsx getting big number , how to get date in excel for mmdd2 ,please let me know

MMDD2 format=dtdate9.,

Re: datetime20. format

Kurt_Bremser — Fri, 11 Sep 2020 18:02:43 GMT

The format you use to display a value does not change the value. When you export to Excel, it exports the stored value.

BTW in Excel, datetimes and dates are stored basically the same, because Excel stores times as fractions of a day, not as counts of seconds as SAS does.

Please show your code you used to export the resulting SAS dataset to Excel.

Re: datetime20. format

Kurt_Bremser — Fri, 11 Sep 2020 18:07:29 GMT

If you want to get a formatted value in Excel, consider using ODS EXCEL and PROC PRINT. Or write a csv file with a DATA step, whery you set the format in the PUT statement.

Re: datetime20. format

rajeshm — Fri, 11 Sep 2020 18:10:23 GMT

proc export data=xx outfile="/bbb.xlsx" dbms=xlsx replace; sheet="firstl";

Re: datetime20. format

rajeshm — Fri, 11 Sep 2020 18:13:56 GMT

without ods and with proc export , can i solve my issue or not?

Re: datetime20. format

Kurt_Bremser — Fri, 11 Sep 2020 19:33:27 GMT

Looks like PROC EXPORT is confused by the DTDATE format; I ran this on UE:

data test;
dt1 = datetime();
dt2 = dt1;
dt3 = datepart(dt1);
format
  dt1 datetime20.
  dt2 dtdate9.
  dt3 date9.
;
run;

proc export
  data=test
  file='/folders/myfolders/test.xlsx'
  dbms=xlsx
  replace
;
run;

and lo and behold! the DT2 column was exported as a raw number instead of being converted to Excel datetime (count of days from 30dec1899, time as fraction of a day).

Bring this to the attention of SAS technical support.

I see three workarounds:

create a date column with DATEPART
use the DATETIME format, and assign a pure date format in Excel after opening the file there (because the values before the decimal point are the same, but be careful because of the "invisible" time part!)
export to a csv file; this will put the formatted values ihn the file, so Excel will see something it can read as a date

PS this was my post # 18.000 here on the communities.