topic Re: SAS datetime informats friendly to excel in SAS Procedures

SAS datetime informats friendly to excel

lyton80 — Mon, 27 Apr 2015 17:18:28 GMT

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.

Re: SAS datetime informats friendly to excel

ballardw — Mon, 27 Apr 2015 17:52:30 GMT

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.

Re: SAS datetime informats friendly to excel

lyton80 — Mon, 27 Apr 2015 22:39:19 GMT

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?

Re: SAS datetime informats friendly to excel

Kurt_Bremser — Tue, 28 Apr 2015 06:14:59 GMT

Some questions remain:

How do you do the export?

What is your Excel locale?

What format(s) are you currently using?

Re: SAS datetime informats friendly to excel

RW9 — Tue, 28 Apr 2015 08:38:06 GMT

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

Re: SAS datetime informats friendly to excel

lyton80 — Tue, 28 Apr 2015 16:35:50 GMT

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.

Re: SAS datetime informats friendly to excel

Tom — Tue, 28 Apr 2015 18:09:06 GMT

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;

Re: SAS datetime informats friendly to excel

Bill — Tue, 28 Apr 2015 20:03:33 GMT

Why not build a Microsoft date from the SASdate?

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

Re: SAS datetime informats friendly to excel

Kurt_Bremser — Wed, 29 Apr 2015 06:02:33 GMT

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.

Re: SAS datetime informats friendly to excel

Peter_C — Wed, 06 May 2015 06:51:28 GMT

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

Re: SAS datetime informats friendly to excel

S_Nav — Mon, 15 Jun 2015 14:47:27 GMT

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

Re: SAS datetime informats friendly to excel

Kurt_Bremser — Mon, 15 Jun 2015 18:35:24 GMT

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

31dec1899 as Excel incorrectly views 1900 as a leapyear.