SAS datetime informats friendly to excel

[lyton80]

Hi,

I am looking SAS date time informats that excel can easily translate.

Presently, the excel extracts generated from SAS can only be sorted as text.

I wish to be able to sort this in an ascending or a descending order as well.

Thanks.

[ballardw]

I think you mean "Format". A SAS Informat is used by SAS to read data.

How are you exporting the data from SAS to Excel?

And you could likely sort it in SAS before / during the creation.

[lyton80]

Sorry, I meant format.

My goal is to use an excel friendly format so that any person can play with the extract I generate at anytime.

At the moment, my date-time fields can only be searched as text and not sorted. So my question is: is there any date-time format which is excel friendly?

[Kurt_Bremser]

Some questions remain:

How do you do the export?

What is your Excel locale?

What format(s) are you currently using?

[lyton80]

How do you do the export?

- I am using proc print imbeded with a  "ods cvs="statement

What is your Excel locale?

- locale?? I am exporting to a folder on my desktop. Hoping I have understood your question.

What format(s) are you currently using?

I am using datetime. formats and anydtdtm40. informats to read the dates into SAS.

This is how it appears in the excel sheet:

11FEB15:12:22:00

This cannot be sorted in an ascending or a descending order in excel.

[Kurt_Bremser]

Excel won't recognize numbers formatted with the SAS default date formats as dates. That is why 11FEB15 is read by Excel as a string.

Tom's method for building an Excel compatible format is a nice way to go.

With "locale", I meant the regional setting of the system where Excel runs, because Excel will also use this setting for determining input types (ie when set to one of the German locales, 123.456.789,123 will be considered a number, which will not happen in any of the English locales)

Bill's suggestion of creating the correct value for Excel with a formula is also very neat, but you will need to set the correct format for the column in Excel.

Be aware that Excel has a problem with dates before March 1, 1900.

[Peter_C]

For a completely different option that can be applied after your SAS datetime default has arrived in excel, consider an excel formula like

'=datevalue(left(##,9))+timevalue(right(##,8))

where ## is cell reference to the datetime string.

Of course I normally choose a method like Tom's

but often find it is too late or limited by others and enterprise guide ..

PeterC

[RW9]

Hi,

ods tagsets.excelxp file="xyz.xlsx" options=(sheet_name="data" autofilter=on);

proc report data=xyz;

     column a date1 date2;

     define     a / "Text column";

     define     date1 / "Date 1" {tagattr='TYPE:DateTime format:mm/dd/yy;@'};

...

run;

Examples given here:

Base SAS: Demo: ExcelXP Tagset and Microsoft Excel

[Tom]

For DATE use YYMMDD10. instead of DATE9. ;

TIME formats should work fine.

For DATETIME  I have found that this PICTURE format works well.  You might want to set different LOW limit to reflect the limitations of SAS and Excel support for old dates.

proc format lib=WORK ;

   picture EXCELDT low-high = "%Y-%0m-%0d %0H:%0M:%0S" (datatype = datetime) ;

run;

[Bill]

Why not build a Microsoft date from the SASdate?

MSDate=SASdate+21916;**Build microsoft date serial;

[S_Nav]

Is there a number to add to a datetime that gives a similar result?

[Kurt_Bremser]

Try '01jan1960:00:00:00'dt - '31dec1899:00:00:00'dt

31dec1899 as Excel incorrectly views 1900 as a leapyear.