topic Re: How to transfer SAS time to Excel in SAS Programming

How to transfer SAS time to Excel

BlayLay — Tue, 06 Oct 2020 13:48:53 GMT

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

Re: How to transfer SAS time to Excel

Kurt_Bremser — Tue, 06 Oct 2020 13:58:41 GMT

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)?

Re: How to transfer SAS time to Excel

BlayLay — Tue, 06 Oct 2020 15:38:53 GMT

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.

Re: How to transfer SAS time to Excel

Kurt_Bremser — Tue, 06 Oct 2020 15:39:30 GMT

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.

Re: How to transfer SAS time to Excel

BlayLay — Tue, 06 Oct 2020 16:00:42 GMT

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.

Re: How to transfer SAS time to Excel

Kurt_Bremser — Tue, 06 Oct 2020 16:21:16 GMT

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.

Re: How to transfer SAS time to Excel

ballardw — Tue, 06 Oct 2020 16:37:13 GMT

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

Re: How to transfer SAS time to Excel

BlayLay — Tue, 06 Oct 2020 16:47:22 GMT

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.

Re: How to transfer SAS time to Excel

Tom — Tue, 06 Oct 2020 23:40:30 GMT

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

Re: How to transfer SAS time to Excel

Tom — Tue, 06 Oct 2020 23:48:39 GMT

@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