Keep format of time

[ElodieT]

Hi,

In my code, I have extracted the time from a datetime by using TIMEPART Function and, I would like to export my table automatically to a folder. Knowing that I have defined this column as HHMM, the output data in SAS shows the time as 08:00 (which is what I want). However, the column in the Excel's export in the folder is written as 1900-01-01 08:00:00

Please, how could I do to keep the format of HHMM. in my export?

Thank you,

[ballardw]

How do you "export" the data? To what file type?

If you create something like CSV you are at Microsoft's mercy as to what will happen when a text file is opened by Excel.

Not much can be done at that point.

If you are writing directly to EXCEL it may be possible to keep the formatting.

Please show the current code you are using to export the data.

[ElodieT]

Hi Ballardw,

Thank you for your quick response.

This is how I do export my data :

Proc export data = nametable

                   outfile = "\\path to the folder\File name.xlsx"

                   dbms=excels replace;

                   port=9621;

                   server='server';

                   serveruser='user';

                   serverpass='password';

;

run;

[mkeintz]

First, if you really are using "dbms=excels ..." then your proc export issue an ERROR message to the log, and would not overwrite a pre-existing excel file.  If that excel file had Jan 1, 1900 already in place, that is what you would see.

But using "excelcs" as below, I get a cell value of 0.33333... to corresponding to the 8:00AM value in SAS, per below:

data have;
  time='08:00't;
  format time time8. ;
  put time=;
run;
proc export data=have
  outfile="c:\temp\have.xlsx"
  dbms=excelcs replace;
run;

Opening it in Windows Excel, it shows 0.33333, which when formatted as time displays 08:00AM.  When formatted as a date, it shows 1900-01-00  (yes, "00").   I don't know whether Excel on the mac shows the same 

[ElodieT]

Hi mKeintz,

It doesn't overwrite yet because these are my first tries before putting it into production...

So if I understand correctly, I would have to change the format of this column manually each time once the excel file has been exported?

Regards,

[mkeintz]

---

@ElodieT wrote:

Hi mKeintz,

It doesn't overwrite yet because these are my first tries before putting it into production...

So if I understand correctly, I would have to change the format of this column manually each time once the excel file has been exported?

Regards,

---

I've never needed to do any work product involving export to Excel, so I'm not qualified to answer your question.  I'm sure there are others on this forum who know all the in's and out's.

[Patrick]

@ElodieT Do you have to use EXCELCS? Below works for me using XLSX

data have;
  time='08:00't;
  format time time8. ;
  put time=;
run;
proc export data=have
  outfile="c:\temp\have.xlsx"
  dbms=xlsx 
  replace;
run;

[ElodieT]

Hi,

Sorry for the late reply. It is the column that I would like to  convert from a datetime to a time. Could I put the name of the column like 'time't to have the whole change? Or is it only for a single value? I tried and it doesn't work for the column.

Regards,

[Patrick]

If you really start with a SAS datetime value then using the timepart() should do what you need.

Below code leads to the result you're after.

data have;
  datetime=datetime();
  time=timepart(datetime);
  format datetime datetime21. time time8. ;
  put time=;
run;
proc export data=have(keep=time)
  outfile="c:\temp\have.xlsx"
  dbms=xlsx 
  replace;
run;