topic Re: Maintaining date-time format when export to Excel file in SAS Programming

Maintaining date-time format when export to Excel file

DocMartin — Thu, 13 May 2021 19:19:59 GMT

I have a SAS data set with date-time values like 28AUG20:10:18:59. Since I want them in a customized format, I wrote the following code:

proc format;
picture dtpic
other='%Y-%0m-%0d %0H:%0M:%0S' (datatype=datetime)
;
data test2; set test1;
   format admitdttm dtpic.;
run;

So my date-time values are like 2020-08-28 10:18:59 which is how I want them to be formatted. The problem comes when I try to export the data set to Excel. I've tried PROC EXPORT, as well as the following:

ods excel file="C:\vvvv\Phase 1\DATA\Results\Gath.xlsx";
proc report data=test2
columns _all_;
run;
ods excel close;

No luck. I'm using SAS 9.4 TS Level 1M2.

Any suggestions?

Thanks!

Andrew

Re: Maintaining date-time format when export to Excel file

ballardw — Thu, 13 May 2021 19:53:06 GMT

So what is the problem?

No values in Excel?

Values but they don't look like the SAS format?

I'm not sure that Excel has a "custom" date time format to handle the converted datetime values. Do you get a "standard" datetime appearance in Excel if you use a basic SAS Datetime format? If so try getting an EXCEL format to display the values properly. Once you know what that looks like you can use a style override for the variable with the TAGATTR option to define the Excel target format.

Re: Maintaining date-time format when export to Excel file

DocMartin — Thu, 13 May 2021 19:58:56 GMT

It depends on how I'm trying to export to Excel. If I use PROC EXPORT I just get the original date-time format that I had used. If I try with ODS Excel, an error occurs in Excel and no data is sent to the file.

Re: Maintaining date-time format when export to Excel file

Tom — Thu, 13 May 2021 20:12:38 GMT

Update your SAS installation. 9.4m2 is almost 7 years old.

https://blogs.sas.com/content/iml/2013/08/02/how-old-is-your-version-of-sas-release-dates-for-sas-software.html

Assuming your admission timestamp was in this century:

proc format;
picture dtpic
other='%Y-%0m-%0d %0H:%0M:%0S' (datatype=datetime)
;
data test2;
   admitdttm = '28AUG2020:10:18:59'dt;
   format admitdttm dtpic.;
run;

ods excel file="C:\downloads\dt.xlsx";
proc report data=test2;
run;
ods excel close;

SAS Results:

Excel Results:

Re: Maintaining date-time format when export to Excel file

DocMartin — Thu, 13 May 2021 20:31:17 GMT

Getting close. Here's what was exported to the Excel file:

hadmit

2020-08-28
10:18:59

I don't know why a carriage return was inserted into the format.

I'm using the version of SAS that my university lets professors have. Ugh!

Re: Maintaining date-time format when export to Excel file

Tom — Thu, 13 May 2021 21:00:24 GMT

Tell them they are wasting their money and your time by forcing you to use out-dated software. They are already paying for the newer version as part of their license fees.

There is an option for tell ODS EXCEL not to insert line breaks to wrap long text strings. But that also is easier with newer version of SAS. I think there was an old workaround for that from before the enhancement added the easy to use option. That might work in your version.

Re: Maintaining date-time format when export to Excel file

Ksharp — Fri, 14 May 2021 13:12:30 GMT

Use special style 'tagattr'.

proc report data=test2 nowd;
define datetime/style={tagattr='format:yyyy/m/d h:mm type:datetime'};
run;

@Cynthia_sas have many post about this style .