How to transfer SAS time to Excel

[BlayLay]

Hi Everyone,

Today's challenge is that I'm bringing in "Production Time" from SAS (formatted as hh:mm:ss $8.) to excel. I was advised that you should bring in time. into excel as character strings, so I did that in SAS before connecting the data to excel. It displays correctly if I look at a subset of the data, however, the entire file is so large that I need to make a SAS connection via a pivot table. This is where the issue begins. When I create the pivot in excel, sum of production by date, I format the Prod Time variable as [h]:mm:ss to get my desired view but it for some reason shows blank in the pivot. Here's a mockup table for an example:

Row Labels	Sum of Prod Time
9/1/2020	0:00:00
9/2/2020	0:00:00
9/3/2020	0:00:00

I've also tried "Count of Prod Time" and that ends up showing hundreds of hours with 0 minutes and 0 seconds (which isn't accurate, since one day should be around 8 hours).

If anyone has any suggestions I would greatly appreciate it. If this isn't the right forum, please advise.

Thanks

[Kurt_Bremser]

PROC EXPORT or LIBNAME XLSX will convert SAS time values (numeric, count of seconds, TIME format) to Excel time values.

Time values formatted as HH:MM:SS in a CSV file will also be recognized by Excel as a time value.

How do you export your SAS data to Excel (code)?

[BlayLay]

I'm currently using a data step to save the SAS table in a folder on the server. And then using the SAS Add-In to connect that SAS table to a pivot.

When I use PROC EXPORT to save the table as a CSV (as the file size exceeds Excel's row limit), Excel is now treating it as a datetime variable but I am not getting an accurate count of hh:mm:ss in the pivot table still.

[Kurt_Bremser]

If you make sure that your time values are stored as such in SAS (not as $8 strings), then the AddIn should get them right in Excel.

[BlayLay]

The time values are stored as Numeric Time. in SAS and show correctly in excel when I subset the data, however, when I try to use a pivot to find the sum of hours on a particular day it's still giving me hundreds of hours (996:00:00) when it should be approximately 8 hours worth (08:00:00). And not allowing me to "sum" because it's a date. Only allows me to "count".

Additionally, when i click into the value in the pivot table, it's treating it as a datetime (2/9/1900 12:00:00 AM) even though I've formatted it as HH:MM:SS in the value field settings.

[Kurt_Bremser]

In Excel, time, date and datetime share the same basic layout. Dates are counts of days, times are fractions of days (12:00:00 is therefore 0.5), and datetimes are counts of days with fractions added. I have no idea why Excel does not let you sum times.

[ballardw]

---

@BlayLay wrote:

I'm currently using a data step to save the SAS table in a folder on the server. And then using the SAS Add-In to connect that SAS table to a pivot.

When I use PROC EXPORT to save the table as a CSV (as the file size exceeds Excel's row limit), Excel is now treating it as a datetime variable but I am not getting an accurate count of hh:mm:ss in the pivot table still.

---

If the data exceeds Excel's row limit then are you sure you should be using Excel at all?? Even with the SAS Add-in I would suspect that the pivot can't use all of the data.(and have performance issues besides).

[BlayLay]

Unfortunately, for my business partner, Excel is the only option to display the desired dashboard. As I mentioned, when I pull a subset of the data onto a sheet, the duration hh:mm:ss are formatted correctly and I even pivoted that subset and was able to sum correctly. However, when I make a pivot to an external connection (the mentioned CSV) the pivot table does not allow me to sum the duration variable.

[Tom]

---

@BlayLay wrote:

Unfortunately, for my business partner, Excel is the only option to display the desired dashboard. As I mentioned, when I pull a subset of the data onto a sheet, the duration hh:mm:ss are formatted correctly and I even pivoted that subset and was able to sum correctly. However, when I make a pivot to an external connection (the mentioned CSV) the pivot table does not allow me to sum the duration variable.

---

Sounds like you need to ask this question on an Excel forum.

What the heck is an "external connection" in Excel?

[Tom]

---

@BlayLay wrote:

I'm currently using a data step to save the SAS table in a folder on the server. And then using the SAS Add-In to connect that SAS table to a pivot.

When I use PROC EXPORT to save the table as a CSV (as the file size exceeds Excel's row limit), Excel is now treating it as a datetime variable but I am not getting an accurate count of hh:mm:ss in the pivot table still.

---

Try removing the TIME format from the variable. That way when you write it to the CSV file it will just be the number of seconds instead a string with colons in it.  If you want it to look like hours then divide by 60*60, either before or after summing.

345   data test;
346     input duration :time. @@;
347     seconds = duration;
348     minutes = seconds/60;
349     hours = minutes/60;
350     format duration tod8. ;
351     put (_all_) (=);
352   cards;

duration=05:00:00 seconds=18000 minutes=300 hours=5
duration=12:30:00 seconds=45000 minutes=750 hours=12.5