Exporting date values to Excel

Reply
Contributor
Posts: 29

Exporting date values to Excel

Hi Everyone,

 

I want to export the dataset having Few Dates column into Excel using ODS. I am able to export the data successfully, however, when I export the dataset to Excel and apply a filter on it, the Date Values appears as shown in Screenshot 1. However, I want to date values to shown as per the screenshot 2 when I apply a filter on it.

 

The Format shown in screenshot 1 does not allow to perform certain calculation though I have applied sas date9. format while exporting. 

 

When the format is a proper date format, as shown in screenshot 2, I can do the those calculation.To bring the date values into proper excel date format, I have to double click on the each cell having date each time. Post that the date values are automatically changed to excel Date format. Is there any way to avoid it?

 

 

Could you please help me.

 

Screenshot 1: 

Screenshot 1.jpg

 

Screenshot 2

 

Screenshot 2.jpg

 

Kind Regards,

Vikrant Sawatkar

Occasional Contributor
Posts: 19

Re: Exporting date values to Excel

I have found the information in this SUGI paper very useful http://www2.sas.com/proceedings/sugi29/068-29.pdf when working with SAS dates and Excel dates. The key is to create a new variable with no SAS format and then apply an Excel format like the one below from a PROC REPORT.

define dtOPEN_Excel / display style(column)={tagattr='format:mm/dd/yyyy'} ;

Super User
Posts: 19,038

Re: Exporting date values to Excel

You should post how your exporting your data, that controls how you specify the format. 

 

Using tagattr is a good way.  Additionall you can try to use mmddyy10 to export rather than date9. format.

Occasional Contributor
Posts: 19

Re: Exporting date values to Excel

Sorry, I am using TAGSETS.EXCELXP for this example so the output is to .xml for use in Excel.

 

ods tagsets.excelxp file= "C:\filename here.xml"

options (autofit_height= 'yes' autofilter= 'all' frozen_headers= '1' row_repeat= '1')

cssstyle= "path to filename.css" ;

proc report data= work.dedup_&edate. missing spanrows nowd ;

columns _all_ ;

define rcrd_id / display ;

compute rcrd_id ;

count+1 ;

if (mod(count,2)) then do ;

call define(_row_, "style","style= [background= cxDBE5F1]") ;

end ;

endcomp ;

define dtOPEN_Excel / display style(column)={tagattr='format:mm/dd/yyyy'} ;

run ;

Contributor
Posts: 29

Re: Exporting date values to Excel

Hi All,

 

Thank you for your kind help.

 

I have tried applying tagattr option to get the result.

 

Please see the code below which I am using.

 

Yet I am not getting the date values appears as per the screenshot 1, after exporting the dataset to excel. I want those values as per the screenshot 2.

 

screenshot 1 -

 

Screenshot 1.jpg

 

Screenshot 2 -

 

2.jpg

 

proc report data = Final2 nowd
style(header) = {rules = all frame = box font_weight = bold just = c verticalalign = m fontsize=3 borderwidth = 1pt backgroundcolor=Blue color=white}
split = '*';
Define Visitor / display;
Define VST1_DT / display style(column)={width=2in tagattr="format:dd-mm-yyyy"};
Define VST2_DT / display style(column)={width=2in tagattr="format:dd/mm/yyyy"};

 

 

Could you please help me


1.jpg
Super User
Super User
Posts: 6,842

Re: Exporting date values to Excel

[ Edited ]

Change the SAS format that you use to display the date.  Excel is too stupid to recognize the DATE9 format and is treating it as a character string.

 

I would recommend using YYMMDD10 format since there will not be any confusing the month and the day part that might happen if you use MMDDYY10 or DDMMYY10.

 

define VST1_DT / display format=yymmdd10. style(column)={width=2in tagattr="format:dd-mm-yyyy"}

Contributor
Posts: 29

Re: Exporting date values to Excel

Sorry for late reply.

 

I have tried applying the above code. but the result is still same. When the dataset is exported to the excel, the format (in excel) shows it as a date format but when I apply filter, I am getting the same result as previous.

 

Occasional Contributor
Posts: 19

Re: Exporting date values to Excel

Have you looked at the link to the SUGI paper on SAS dates? http://www2.sas.com/proceedings/sugi29/068-29.pdf Specifically the section on converting SAS dates to Excel dates, in my experience you need to create a new variable with no SAS format even in the PROC REPORT that way Excel does the interpretation. If you create a new variable with no date or datetime format and then use the ODS TAGSETS.EXCELXP in PROC REPORT with a TAGATTR format Excel will interpret the date as a date and allow for grouping in a pivot table.

 

Ask a Question
Discussion stats
  • 7 replies
  • 707 views
  • 2 likes
  • 4 in conversation