Exporting SAS Data to Excel and Retaining Date Numeric Type

[djbateman]

I am trying to export a bunch of datasets from SAS to Excel.  I am sure this is a little outdated, but we are using ODS TAGSETS to get the data into Excel.  We are stuck using a specific template, and we do not yet have the latest maintenance in SAS to make the same styles in just PROC EXPORT.

That said, my datasets have date variables that are in DATE9. format.  But when the Excel file is created, they are stored as character strings.  That means that when the reviewer wants to filter or sort the data, they cannot do so chronologically but rather alphabetically.  Can someone tell me if there is some kind of trick to get Excel to retain the numeric properties of the date rather than converting to a character string?  Below is the code I am using, but I have included both PROC REPORT and PROC PRINT options, as I'm not sure if one is preferred over the other with what I'm trying to accomplish.

ods listing close;
ods tagsets.excelxp file="S:\cdm\Programming\Adhoc\Weekly_Dashboard\Output\ExecutiveSummary_&program.-&study._&sysdate9..xml" style=work.newxls;
ods escapechar='~';

ods tagsets.excelxp options(&ods_xls_options. sheet_name="Discontinuation" absolute_column_width='12,11,11,13,15,25,11');
	title4 j=l "Discontinuation Due to AE Listing";
	proc report data=discon_list;
		column subj_sex_age discondt_sd discondt discondt_sdd dsdiscs dsaes_sp createddatetime;
		define subj_sex_age / display 'Subject (Sex/Age)';
		define discondt_sd / display 'Discontinuation Study Day';
		define discondt / display 'Discontinuation Date';
		define discondt_sdd / display 'Discontinuation Study Day (Date)';
		define dsdiscs / display 'Reason for Discontinuation';
		define dsaes_sp / display 'AE Leading to Discontinuation';
		define createddatetime / display 'Record Creation Date/Time';
	run;
	proc print data=discon_list noobs label;
		var subj_sex_age discondt_sd discondt discondt_sdd dsdiscs dsaes_sp createddatetime / style={tagattr="WRAP:YES"} style(data)={tagattr='format:@'};
		label	discondt_sd='Discontinuation Study Day'
				discondt='Discontinuation Date'
				discondt_sdd='Discontinuation Study Day (Date)'
				dsdiscs='Reason for Discontinuation'
				dsaes_sp='AE Leading to Discontinuation'
				createddatetime='Record Creation Date/Time';
	run;

[Reeza · Accepted solution]

You need a SAS date and then apply the format with the PROC REPORT, as well as the TAGATTR option as well though to get it to export properly as a date. 

Is that an option for you or does that not work for some reason?

data test;                       
   dateval= dhms('01Jan09'd,0,0,0);   
   var1 = 3;                     
   var2 = 3;                     
   output;                           
   dateval = dhms('01Feb09'd,0,0,0);   
   var1 = 5;                     
   var2 = 6; 
   dateval = dhms('01Mar09'd,0,0,0);   
   var1 = 3;                     
   var2 = 4;  
   output;                             
   format dateval IS8601DT.;   
run;                                                           
                                                                
options nodate missing=' ';
                                    
ods tagsets.excelxp file="c:\temp\temp.xls"     
    options(embedded_titles= 'yes');                                                  
                                                                
proc print data=test;                         
   var dateval /                                                  
      style(data)={tagattr='TYPE:DateTime format:mm/dd/yy;@'};  
      title "Modifying the data type and format of a date value";
   var var1 var2;                                         
   format dateval  IS8601DT.;                        
run;                                            

ods tagsets.excelxp close;                        

https://support.sas.com/kb/38/143.html

[djbateman]

Reeza,

I may be way off, but I tried to modify my code as shown below, and I got a pop-up box when trying to open the Excel file:  "Problems came up in the following areas during load: Style, Table".

I have 2 variables that might need formatted.  DISCONDT is a date9. variable and for sure needs to be altered.  CREATEDDATETIME is a date/time variable and isn't crucial to be altered.  I'm mostly trying to focus on DISCONDT.  Can you let me know if I updated my code properly?  I'm obviously missing something.

ods listing close;
ods tagsets.excelxp file="S:\cdm\Programming\Adhoc\Weekly_Dashboard\Output\ExecutiveSummary_&program.-&study._&sysdate9..xml" style=work.newxls;
ods escapechar='~';

ods tagsets.excelxp options(&ods_xls_options. sheet_name="Discontinuation" absolute_column_width = '12,11,11,13,15,25,11');
	title4 j=l "Discontinuation Due to AE Listing";
	proc print data=discon_list noobs label;
		var subj_sex_age discondt_sd / style={tagattr="WRAP:YES"} style(data)={tagattr='format:text'};
		var discondt / style(data)={tagattr='TYPE:Date format:ddmmmyyyy@'};
		var discondt_sdd dsdiscs dsaes_sp / style={tagattr="WRAP:YES"} style(data)={tagattr='format:text'};
		var createddatetime / style(data)={tagattr='TYPE:DateTime format:mm/dd/yy;@'};;
		label	subj_sex_age='Subject (Sex/Age)'
				discondt_sd='Discontinuation Study Day'
				discondt='Discontinuation Date'
				discondt_sdd='Discontinuation Study Day (Date)'
				dsdiscs='Reason for Discontinuation'
				dsaes_sp='AE Leading to Discontinuation'
				createddatetime='Record Creation Date/Time';
	run;

[Reeza]

First question - did the example run correctly as is?

[djbateman]

Yes, it worked great. Even the filter showed the proper year/month/day sections to filter.

[SASKiwi]

Looks like you missed out the semicolon before the ampersand in this statement:

var discondt / style(data)={tagattr='TYPE:Date format:ddmmmyyyy@'}; * Your version;

var discondt / style(data)={tagattr='TYPE:Date format:ddmmmyyyy;@'}; * Corrected version;

[ballardw]

---

@djbateman wrote:

Reeza,

I may be way off, but I tried to modify my code as shown below, and I got a pop-up box when trying to open the Excel file:  "Problems came up in the following areas during load: Style, Table".

I have 2 variables that might need formatted.  DISCONDT is a date9. variable and for sure needs to be altered.  CREATEDDATETIME is a date/time variable and isn't crucial to be altered.  I'm mostly trying to focus on DISCONDT.  Can you let me know if I updated my code properly?  I'm obviously missing something.

ods listing close;
ods tagsets.excelxp file="S:\cdm\Programming\Adhoc\Weekly_Dashboard\Output\ExecutiveSummary_&program.-&study._&sysdate9..xml" style=work.newxls;
ods escapechar='~';

ods tagsets.excelxp options(&ods_xls_options. sheet_name="Discontinuation" absolute_column_width = '12,11,11,13,15,25,11');
	title4 j=l "Discontinuation Due to AE Listing";
	proc print data=discon_list noobs label;
		var subj_sex_age discondt_sd / style={tagattr="WRAP:YES"} style(data)={tagattr='format:text'};
		var discondt / style(data)={tagattr='TYPE:Date format:ddmmmyyyy@'};
		var discondt_sdd dsdiscs dsaes_sp / style={tagattr="WRAP:YES"} style(data)={tagattr='format:text'};
		var createddatetime / style(data)={tagattr='TYPE:DateTime format:mm/dd/yy;@'};;
		label	subj_sex_age='Subject (Sex/Age)'
				discondt_sd='Discontinuation Study Day'
				discondt='Discontinuation Date'
				discondt_sdd='Discontinuation Study Day (Date)'
				dsdiscs='Reason for Discontinuation'
				dsaes_sp='AE Leading to Discontinuation'
				createddatetime='Record Creation Date/Time';
	run;

---

If your Createdatetime is a SAS date time value then when you attempt to apply the mmddyy format you get nothing but **** as it does not fit. Run this code and look in the log:

data _null_;
   dt = '03Mar2021:14:25:27'dt;
   put "Date format with datetime value: " dt=date9.;
   put "Datetime format with datetime value: " dt=datetime18.;
run;

You will get a similar result with any date format if the value is a SAS datetime value unless the value is much closer to the year 1960. Then you get wildly wrong "years" because the value of datetime is stored as seconds and dates are days.

Example:

data _null_;
   dt = '01JAN1960:14:25:27'dt;
   put "Date format with datetime value: " dt=date9.;
   put "Datetime format with datetime value: " dt=datetime18.;
run;

I think for DDMMMYY equivalent format that Excel may want d-mmm-yy

if that works then the datetime may want m/d/yyyy h:mm