topic Re: Export a DB in an excel file - format "time" in SAS Programming

Export a DB in an excel file - format "time"

Tecla1 — Tue, 05 May 2020 10:19:24 GMT

Good morning,

I have a little problem but I'm not able to resolve it. I need to export a DB with columns with format "Time", the excel file don't keep the right format but take the "date" format, so I have to change the format in all columns....

Please, can you give me some suggestion?

Many tnks for you kindly help.

Tecla

Re: Export a DB in an excel file - format "time"

Kurt_Bremser — Tue, 05 May 2020 10:27:34 GMT

This works:

data test;
mytime = time();
format mytime time8.;
run;

proc export
  data=test
  file='/folders/myfolders/mytime.xlsx'
  dbms=xlsx
  replace
;
run;

at least the value is a time value in the spreadsheet, just that the format is shorter (equivalent to time5) when opening the XLSX in LibreOffice.

Please post the code with which you create the time variable in SAS, and how you export it.

Re: Export a DB in an excel file - format "time"

Tecla1 — Tue, 05 May 2020 11:06:11 GMT

Hi Kurt,

tnk for your replay, as your request:

data Vsf_accounting_vpn_g;

set QUERY_FOR_FILTER_FOR_VSF_ACCOUNT;

format ORA_PRIMO_COLL ORA_LAST_COLL TIMEW. GG_PRIMO_COLL GG_LAST_COLL DDMMYY10.;

/* Ora prima connessione */

ORA_PRIMO_COLL = timepart(TIMESTAMP_CONNESSIONE);

/* Ora ultima connessione */

ORA_LAST_COLL = timepart(TIMESTAMP_DISCONNESSIONE);

GG_PRIMO_COLL = datepart(TIMESTAMP_CONNESSIONE);

GG_LAST_COLL = datepart(TIMESTAMP_DISCONNESSIONE);

run;

PROC EXPORT DATA= rwork.Vsf_accounting_vpn_g

OUTFILE= "\\grpi-sas-dgc-vip.cariprpc.it\SASDGSWRETE_READ_WRITE\20_dati\report\prova.xlsx"

DBMS=EXCEL REPLACE;

SHEET="PF_005";

RUN;

Re: Export a DB in an excel file - format "time"

Tecla1 — Tue, 05 May 2020 11:08:16 GMT

excuse me.... the ora format in wrong .... I used "time." ... "timew," was an attempt !!!

Re: Export a DB in an excel file - format "time"

Kurt_Bremser — Tue, 05 May 2020 11:56:18 GMT

Use dbms=xlsx instead of dbms=excel.

Note that Excel does not make a distinction between date and time values internally. Times are stored as fractions of days (not as counts of seconds like SAS and database systems do). 0.5 is the time of 12:00:00, but also the timestamp 1899-12-31:12:00:00 in Excel parlance.