BookmarkSubscribeRSS Feed
lyton80
Obsidian | Level 7

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.

11 REPLIES 11
ballardw
Super User

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
Obsidian | Level 7

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?

lyton80
Obsidian | Level 7

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
Super User

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
Rhodochrosite | Level 12

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User Tom
Super User

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
Quartz | Level 8

Why not build a Microsoft date from the SASdate?

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

S_Nav
Calcite | Level 5

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

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 5380 views
  • 7 likes
  • 8 in conversation