topic Re: Grouping dates in Excel output in Microsoft Integration with SAS

Grouping dates in Excel output

vegan_renegade — Thu, 19 Nov 2020 19:28:21 GMT

UPDATE: Issue fixed, though not sure what I did to fix it hah.

Hello all,

I am successfully exporting a dataset to XLSX using the code below in SAS 9.4. However, in the Collection Date column of the exported file, the dates in the filter are all listed ungrouped...it's just a long list of dates. I'd like to know how to export it so that the days are auto grouped by month and day that I can expand and collapse.

The Collection Date variable in SAS is numeric mmddyy10. format, informat 10., (shown as 03/05/2020 in the XLSX output). I have a feeling either I need to add an option in tagset or change the Collection Date variable to a format that Excel will understand and thus group automatically.

/**************/
/*Export files*/
/**************/

/*Location of Excel file output*/
ods noresults; 
ods excel file='PATH.XLSX' style=Printer;

/*Options and variables*/
ods excel options
(sheet_name = "COVID Data by Day" frozen_headers="yes" autofilter="1-2" absolute_column_width="15,35,10,10,10,10,10");

title "COVID Data Report - Totals by Date and Testing Location";
proc report data=counts_date1
style(header) = {background=#32312F foreground=white font_weight=bold};
columns Collection_Date Draw_Location Negative Positive Other Total Positivity_Rate /*Nares Nasopharyngeal Other_sample_type Unknown_sample_type*/;
define Collection_Date / "Collection Date";
define Draw_Location / "Testing Location";
define Positivity_Rate / "Positivity Rate";
/*define Other_sample_type / "Other sample type";*/
/*define Unknown_sample_type / "Unknown sample type";*/
run;
title;

ods excel close;

Re: Grouping dates in Excel output

Reeza — Thu, 19 Nov 2020 17:35:35 GMT

You either need to manually add a variable like that but then Excel will likely treat it as a character not a date, which means that you need to then control the display order somehow.

And I believe Excel does that now for anything it recognizes as a date so it's a pivot table setting that needs to change.

You need to right click on the field in the pivot table, select grouping and then in the BY section select how you'd like to group it.

At least this was the solution I found a few weeks ago when dealing with the issue. Looking forward to seeing what other people have to say.

@vegan_renegade wrote:

Hello all,

I am successfully exporting a dataset to XLS using the code below. However, in the Collection Date column of the exported file, the dates in the filter are all listed ungrouped...it's just a long list of dates. I'd like to know how to export it so that the days are auto grouped by month and day that I can expand and collapse.

The Collection Date variable in SAS is numeric mmddyy10. format, informat 10., (shown as 03/05/2020 in the XLS output). I have a feeling either I need to add an option in tagset or change the Collection Date variable to a format that Excel will understand and thus group automatically.
/**************/
/*Export files*/
/**************/

/*Location of Excel file output*/
ods noresults; 
ods tagsets.ExcelXP file='PATH.XLS' style=Printer;

/*Options and variables*/
ods tagsets.excelxp options
(sheet_name = "COVID Data by Day" frozen_headers="yes" autofilter="1-2" absolute_column_width="15,35,10,10,10,10,10");

title "COVID Data Report - Totals by Date and Testing Location";
proc report data=counts_date1
style(header) = {background=#32312F foreground=white font_weight=bold};
columns Collection_Date Draw_Location Negative Positive Other Total Positivity_Rate /*Nares Nasopharyngeal Other_sample_type Unknown_sample_type*/;
define Collection_Date / "Collection Date";
define Draw_Location / "Testing Location";
define Positivity_Rate / "Positivity Rate";
/*define Other_sample_type / "Other sample type";*/
/*define Unknown_sample_type / "Unknown sample type";*/
run;
title;

ods tagsets.ExcelXP close;

Re: Grouping dates in Excel output

vegan_renegade — Thu, 19 Nov 2020 18:14:25 GMT

@Reeza Thanks, but there is no pivot table. With so many options available at export, I'm sure there's a way to make these grouped automatically rather than manually.

Re: Grouping dates in Excel output

vegan_renegade — Thu, 19 Nov 2020 19:24:11 GMT

Update: I played around with a few export options and formats, and I found it worked in CSV format, but since CSV does not save formatting and colors, I reverted back all the changes..and magically the file now exports with the dates grouped in XLSX. Not sure what I did but we are good haha.

Re: Grouping dates in Excel output

Reeza — Thu, 19 Nov 2020 21:19:44 GMT

I realize you're OK with it just being alphabetical so you can just use a character format for the date if you wanted.