Help using Base SAS procedures

SAS datetime informats friendly to excel

Reply
Contributor
Posts: 20

SAS datetime informats friendly to excel

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.

Super User
Posts: 11,343

Re: SAS datetime informats friendly to excel

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.

Contributor
Posts: 20

Re: SAS datetime informats friendly to excel

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?

Super User
Posts: 7,809

Re: SAS datetime informats friendly to excel

Some questions remain:

How do you do the export?

What is your Excel locale?

What format(s) are you currently using?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 20

Re: SAS datetime informats friendly to excel

Posted in reply to KurtBremser

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.

Super User
Posts: 7,809

Re: SAS datetime informats friendly to excel

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 2,177

Re: SAS datetime informats friendly to excel

Posted in reply to KurtBremser

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

Super User
Super User
Posts: 7,970

Re: SAS datetime informats friendly to excel

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='TYPESmiley Very HappyateTime format:mm/dd/yy;@'};

...

run;

Examples given here:

Base SAS: Demo: ExcelXP Tagset and Microsoft Excel

Super User
Super User
Posts: 7,060

Re: SAS datetime informats friendly to excel

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;

Super Contributor
Posts: 292

Re: SAS datetime informats friendly to excel

Why not build a Microsoft date from the SASdate?

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

New Contributor
Posts: 2

Re: SAS datetime informats friendly to excel

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

Super User
Posts: 7,809

Re: SAS datetime informats friendly to excel

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

31dec1899 as Excel incorrectly views 1900 as a leapyear.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 11 replies
  • 1340 views
  • 6 likes
  • 8 in conversation