Maintaining date-time format when export to Excel file

[DocMartin]

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

[ballardw]

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.

[DocMartin]

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.

[Tom]

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-so...

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:

[DocMartin]

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!

[Tom]

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.

[Ksharp]

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 .