topic Re: Write SAS Time fields to Excel - loses time format in SAS Programming

Write SAS Time fields to Excel - loses time format

bnawrocki — Thu, 01 Jul 2021 20:38:46 GMT

I have a SAS file with field named MYTIME which is Numeric and TIME. format associated. I want to write this to Excel, letting a co-worker edit some other fields, then read back into SAS. Problem is importing back into SAS, MYTIME is assigned TEXT type.

To write from SAS to Excel, and make sure format of MYTIME is kept as a time in Excel, I use:

ods excel file="myfile.xlsx";
   proc print data=mydata;
      var A B C;
      var MYTIME / style(data)={tagattr='format:h:mm'};
   run;
ods excel close;

When I open Excel, it has correctly assigned a format of h:mm to the MYTIME column, so that looks good, but when I re-import the Excel file into SAS using this code...

proc import file="myfile.xlsx" dbms=xlsx out=test_read replace; run;

... MYTIME now is type TEXT with format $8., not NUMERIC with TIME. format!

If I build an Excel file from scratch and enter a time into a cell, it correctly imports to SAS using PROC IMPORT code above, so something different is happening when I create the Excel file via SAS, but what?

Re: Write SAS Time fields to Excel - loses time format

ChrisNZ — Fri, 02 Jul 2021 00:17:08 GMT

Try adding

format MYTIME time.;

to your proc print.

Re: Write SAS Time fields to Excel - loses time format

bnawrocki — Fri, 02 Jul 2021 13:05:04 GMT

Thanks, ChrisNZ. However, I already tried the FORMAT statement for the MYTIME variable - that was the first version of my SAS code. However, the results are the same - when I read it back into SAS from Excel, PROC IMPORT sets MYTIME to text. In Excel, the MYTIME cells look OK, but are formatted as "Custom: #######0".

I'll try a couple other approaches today - PROC REPORT and PROC EXPORT.

Re: Write SAS Time fields to Excel - loses time format

ChrisNZ — Sat, 03 Jul 2021 02:11:43 GMT

It works fine for me. Try to see what differs between your process and this one:

filename XL "myfile.xlsx";
  
ods excel file=XL;
proc print data=SASHELP.CLASS noobs;
  var NAME SEX;
  var AGE / style(data)={tagattr='format:h:mm'};
  format AGE time.;
run;
ods excel close;

proc import file=XL dbms=xlsx out=READ replace; 
proc print data=READ; run;

Obs	Name	Sex	Age
1	Alfred	M	0:00:14
2	Alice	F	0:00:13
3	Barbara	F	0:00:13

Re: Write SAS Time fields to Excel - loses time format

Patrick — Sat, 03 Jul 2021 06:07:49 GMT

@ChrisNZ Using your code I believe the challenge the OP raises is the data type of Age becoming character instead of numeric.

Re: Write SAS Time fields to Excel - loses time format

ErikLund_Jensen — Sat, 03 Jul 2021 09:22:22 GMT

Hi @bnawrocki

The trick here is to convert the Time Value to something Excel understands before writing data to Excel.

A Time Value is represented in Excel as a fraction of a day, but in SAS it is a number of seconds. So - given the input is a SAS Time Value - the SAS Time Value,should be divided by 86400 (the number of seconds in a day) to get the fraction. Then everything works

data mydata;
	a = 1; b=2; c=3;
	textvar = 'anyvalue';
	mytime = '17:28:00't;
	mytime = mytime/86400;
run;

ods excel file="c:\temp\test.xlsx";
   proc print data=mydata;
      var A B C;
      var MYTIME / style(data)={tagattr='format:h:mm'};
   run;
ods excel close;
proc import file="c:\temp\test.xlsx" dbms=xlsx out=test_read replace; 
run;

Excel:

SAS Import:

SAS data set attributes:

Re: Write SAS Time fields to Excel - loses time format

Tom — Sat, 03 Jul 2021 14:29:06 GMT

I cannot recreate your issue. Are you using an older version of SAS where ODS EXCEL is experimental? Check the version (including the M# ).

1460  %put &=sysvlong;
SYSVLONG=9.04.01M5P091317

Did you modify the XLSX file with Excel before trying to read it?

Re: Write SAS Time fields to Excel - loses time format

ErikLund_Jensen — Sat, 03 Jul 2021 16:04:54 GMT

@Tom

It doesn't seem to be a version issue. I use SAS 9.04.01M7P080520, and I get the same result as @bnawrocki when I run the posted code. The Time variable is imported from the XLSX file as a text.

I and I have tried running the code on a Windows X64_10Pro platform and also on a Red Hat Linux version 7.9 with the same result:

152   proc import file="/sasdata/udvk/work/test.xlsx" dbms=xlsx out=test_read replace;
153   run;

NOTE: VARCHAR data type is not supported by the V9 engine. Variable mytime has been converted
      to CHAR data type.
NOTE: The import data set has 1 observations and 5 variables.
NOTE: WORK.TEST_READ data set was successfully created.

Re: Write SAS Time fields to Excel - loses time format

Tom — Sat, 03 Jul 2021 16:54:40 GMT

Seems to be a bug introduced in 9.4m6.

This code works fine in 9.4m5 on both Windows and Unix. But fails in m6 and m7 on Unix.

data test;
  do hour=1 to 5 ;
    time=hour*60*60 ;
    output;
  end;
  format time time8.;
run;

%let fname=%sysfunc(pathname(work))/example1.xlsx;

ods excel file="&fname" options (sheet_name='Sheet1');
proc print data=test noobs;
 var hour;
 var time / style(data)={tagattr='format:h:mm'};
run;
ods excel close ;

libname x xlsx "&fname";

proc contents data=x._all_;
run;

proc import file="&fname" dbms=xlsx out=test_read replace;
run;

proc contents data=test_read ;
run;

Results:

>grep time m?.lst
m5.lst:hour        time
m5.lst:     Obs    hour     time
m5.lst:2    time        Num       8    TIME.     time 
m5.lst:2    time        Num       8    TIME.     time 
m6.lst:hour        time
m6.lst:     Obs    hour    time
m6.lst:2    time        Char      7    $7.       $7.         time 
m6.lst:2    time        Char      7    $7.       $7.         time 
m7.lst:hour        time
m7.lst:     Obs    hour    time
m7.lst:2    time        Char      7    $7.       $7.         time 
m7.lst:2    time        Char      7    $7.       $7.         time

Re: Write SAS Time fields to Excel - loses time format

Patrick — Sun, 04 Jul 2021 03:27:07 GMT

It also fails for Windows 9.4M7 workstation

Current version: 9.04.01M7P080520
Operating System:   WX64_WKS.

Re: Write SAS Time fields to Excel - loses time format

Patrick — Sun, 04 Jul 2021 08:59:22 GMT

@bnawrocki , @Tom

I couldn't find any SAS or defect Note for this issue so raised a SAS TechSupport track. I'll post proceedings here once received.

Re: Write SAS Time fields to Excel - loses time format

ErikLund_Jensen — Sun, 04 Jul 2021 09:50:16 GMT

Hi @bnawrocki , @Tom & @Patrick

There is a workaround so you can get your code working in M7 (Win and Linux) while you wait for a hotfix.

I added a step to Tom's code to convert the SAS time to an Excel time and remove the TIME Format from the variable. Then it works.

data test;
  do hour=1 to 5 ;
    time=hour*60*60 ;
    output;
  end;
  format time time8.;
run;

* Step added to fix problem; 
* Format statement placed before Set statement to remove the existing time format;
data test; 
	format time 8.;
	set test;
	time = time/86400;
run;

%let fname=%sysfunc(pathname(work))/example1.xlsx;

ods excel file="&fname" options (sheet_name='Sheet1');
proc print data=test noobs;
 var hour;
 var time / style(data)={tagattr='format:h:mm'};
run;
ods excel close ;

libname x xlsx "&fname";

proc contents data=x._all_;
run;

proc import file="&fname" dbms=xlsx out=test_read replace;
run;

proc contents data=test_read ;
run;

- And I am aware that using the same data set as input and output isn't best practice, but I took the liberty here to avoid making any changes in Tom's code.

Re: Write SAS Time fields to Excel - loses time format

ChrisNZ — Mon, 05 Jul 2021 02:58:10 GMT

9.2M2 on Windows here. I obtain different results.

The variable is never seen as a string, but is imported as number of hours instead of number of minutes if the time format is not applied.

That's because the custom Excel format strips the seconds.

When the format is applied, all works as expected (hence my suggestion earlier). Successive versions seem to behave differently.

ods excel file="&wdir\myfile.xlsx";
   proc print data=SASHELP.CLASS(obs=3) noobs;
      var NAME SEX;         
      format AGE time.;
      var AGE / style(data)={tagattr='format:hh:mm'};
   run;
ods excel close;
proc import file="&wdir\myfile.xlsx" dbms=xlsx out=T replace; run;
proc print; run;
proc contents; run;

ods excel file="&wdir\myfile.xlsx";
   proc print data=SASHELP.CLASS(obs=3) noobs;
      var NAME SEX;         
      * format AGE time.;  ******************************* format commented out; 
      var AGE / style(data)={tagattr='format:hh:mm'};
   run;
ods excel close;
proc import file="&wdir\myfile.xlsx" dbms=xlsx out=T replace; run;
proc print; run;
proc contents; run;

Name	Sex	Age
Joyce	F	0:00:11
Thomas	M	0:00:11
James	M	0:00:12

Obs	Name	Sex	Age
1	Joyce	F	0:00:11
2	Thomas	M	0:00:11
3	James	M	0:00:12

Engine/Host Dependent Information
Data Set Page Size	131072
Number of Data Set Pages	2
Number of Data Set Repairs	0
Filename	K:\SASWORK\_TD9924_NZ8037SPSAS2003_\Prc2\t.sas7bdat
Release Created	9.0401M2
Host Created	X64_SRV12

Alphabetic List of Variables and Attributes
#	Variable	Type	Len	Format	Informat	Label
3	Age	Num	8	TIME.		Age
1	Name	Char	6	$6.	$6.	Name
2	Sex	Char	1	$1.	$1.	Sex

Name	Sex	Age
Joyce	F	11
Thomas	M	11
James	M	12

Obs	Name	Sex	Age
1	Joyce	F	264:00
2	Thomas	M	264:00
3	James	M	288:00

Engine/Host Dependent Information
Data Set Page Size	131072
Number of Data Set Pages	2
Number of Data Set Repairs	0
Filename	K:\SASWORK\_TD9924_NZ8037SPSAS2003_\Prc2\t.sas7bdat
Release Created	9.0401M2
Host Created	X64_SRV12

Alphabetic List of Variables and Attributes
#	Variable	Type	Len	Format	Informat	Label
3	Age	Num	8	TIME.		Age
1	Name	Char	6	$6.	$6.	Name
2	Sex	Char	1	$1.	$1.	Sex

Re: Write SAS Time fields to Excel - loses time format

bnawrocki — Fri, 09 Jul 2021 18:37:01 GMT

A hearty thank you to everyone who has responded to my question. The workaround code (see Accepted Solution) is working for me, so I'm happy!

Re: Write SAS Time fields to Excel - loses time format

bnawrocki — Fri, 09 Jul 2021 19:19:40 GMT

Spoke a bit too soon.

SAS still isn't reading a TIME column back in as a Numeric time value if any of the values in that TIME column are missing (blank). It assigns the entire column as Text. I'll try a few more output options...

Re: Write SAS Time fields to Excel - loses time format

bnawrocki — Fri, 09 Jul 2021 20:05:07 GMT

Got around the missing TIME values by adding this code to force a 00:00 time value into the field if the time is missing, before writing it to Excel. That field now correctly IMPORTs from Excel back into SAS as a Time field.

if missing(MYTIME) then MYTIME= '0:0't;

Re: Write SAS Time fields to Excel - loses time format

Patrick — Mon, 12 Jul 2021 02:51:32 GMT

Just as an update: This issue has now been raised with SAS R&D.