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
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.
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.
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.