topic Re: Keep format of time in SAS Programming

Keep format of time

ElodieT — Fri, 30 Jul 2021 20:13:18 GMT

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,

Re: Keep format of time

ballardw — Fri, 30 Jul 2021 20:57:11 GMT

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.

Re: Keep format of time

ElodieT — Fri, 30 Jul 2021 21:08:01 GMT

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;

Re: Keep format of time

mkeintz — Fri, 30 Jul 2021 21:29:55 GMT

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

Re: Keep format of time

ElodieT — Fri, 30 Jul 2021 21:39:11 GMT

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,

Re: Keep format of time

mkeintz — Fri, 30 Jul 2021 22:13:14 GMT

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

Re: Keep format of time

Patrick — Sat, 31 Jul 2021 01:27:55 GMT

@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;

Re: Keep format of time

ElodieT — Thu, 05 Aug 2021 01:48:14 GMT

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,

Re: Keep format of time

Patrick — Thu, 05 Aug 2021 03:01:01 GMT

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;