topic Formatted variables incorrectly outputting to excel in SAS Programming

Formatted variables incorrectly outputting to excel

RoddyJ — Fri, 21 Feb 2020 15:02:58 GMT

Hi all,

I've calculated a time difference between two datetime variables in a dataset. When I try to export it Excel, the format of the time difference are incorrect and inconsistent. I'd like them to be output in HH:MM format.

The code I'm using is as follows:

data time_diffs;
set times;
diff = t1 - t2;
/* format diff hhmm. */
run;

proc means;
class by_var;
var diff;
output out=median_diffs median(diff)=diff;
run;

data diffs_output;
set median_diffs
diff_text = put(diff, hhmm.);
run;

I'm using Enterprise guide and export with the export button in the Output Data tab to an xlsx.

In the xlsx some values are just text as expected, but some have the time as a number.

Can anyone help?

Re: Formatted variables incorrectly outputting to excel

Tom — Fri, 21 Feb 2020 15:01:31 GMT

You need to explain more what you mean by "button in the Output Data tab".

First how are you running your SAS code? Sounds like you are using some type of point and click interface. Which one? Enterprise Guide? SAS/Studio? Something else?

Re: Formatted variables incorrectly outputting to excel

RoddyJ — Fri, 21 Feb 2020 15:02:02 GMT

Enterprise guide, will update post

Re: Formatted variables incorrectly outputting to excel

Tom — Fri, 21 Feb 2020 16:09:34 GMT

Show an example of what is wrong. It is not clear what variable you are talking about. You don't show a variable named TIME.

Your code generates dataset TIME_DIFFS, MEDIAN_DIFFS and DIFF_OUTPUT. Which of these are you exporting?

Your code mentions variables named T1, T2, DIFF, BY_VAR, and DIFF_TEXT. Which of these are showing properly? Which are not?