Preserve a date format in Proc Report export to Excel

[amiller]

Hello,

I have read some posts on this topic, but nothing is working for me.

I have a dataset with a variable called cal_date that is numeric with a date format (date11).

In SAS 9.4 Proc Report I am using ODS tagsets and the define statement is as follows: DEFINE cal_date/display 'Calendar Date' format=date11.;

I have tried this too - DEFINE cal_date/display 'Calendar Date' format=date11. style(column) = {tagattr="format:dd-mmm-yyyy"};

But in the excel output, I want to be able to use the date filter on this column and I cannot. It just gives me the text filter which is not helpful.

Any suggestions?

Thanks!

[PaigeMiller]

I am not experiencing this type of difficulty in Excel.

ods excel file='text.xlsx';
proc report data=sashelp.citimon(obs=20);
    columns date cciuac cciutc;
    define date/display format=date11. "Date";
run;
ods excel close;

[amiller]

PaigeMiller

When you run that code and filter on the date column you see the Date Filter and not the Text Filter?

When I use this:

DEFINE cal_date/display format=date11. "Calendar Date" ;

In excel, I get the text filter and want to be able to use the date filter.

[Ksharp]

You aslo could try "type:date" :

ods excel file='c:\temp\text.xlsx';
proc report data=sashelp.stocks(obs=20);
    columns stock date close;
    define date/display format=date11. "Date" 
style(column) = {tagattr="type:date format:dd-mmm-yyyy"};
run;
ods excel close;

[amiller]

KSharp:

When I add "type:date" just as you have listed it, I get a table error and cannot open the excel file.

[Ksharp]

Post your data and sas code to let us test your problem.
And by the way what sas version are you using ?

[amiller]

This is my data:

And my proc report:

ods _all_ close;

ods tagsets.excelxp path="&studydir&study\Data Management\RecordStatus\Output" file="TempReport_&sysdate9..xml"

style= seaside options(embedded_titles='yes' embedded_footnotes='yes' ); 

ods tagsets.ExcelXP options(Autofilter = "Yes" Frozen_headers = "Yes" Doc = 'Help' sheet_name='RecStat' orientation='landscape' row_repeat='1-2');

PROC REPORT DATA=temp nowd SPLIT="*"

STYLE (SUMMARY) = [COLOR=WHITE BACKGROUNDCOLOR=ALICEBLUE font_weight=bold];

  COLUMNS subjectid  cal_date;

DEFINE Subjectid/display 'Subject' style=[cellwidth= .85 in];

*DEFINE cal_date/display 'Calendar Date'  style(column)={tagattr="format:dd-mmm-yyyy"};

/*format=date11. style(column) = {tagattr="format:dd-mmm-yyyy"};*/

DEFINE cal_date/display format=date11. "Calendar Date" style(column)={tagattr="type:Date format:dd-mmm-yyyy;@"};

run;

quit;

ods listing close;

[Tom]

Why are you making an XML file instead of an XLSX file?  And you did not CLOSE the ODS destination.

Your code works fine when using ODS EXCEL.  Why aren't you using that?

data temp;
  do cal_date='01JUN2022'd to '01MAR2023'd by 25 ;
    subjectid+1;
    output;
  end;
run;

ods excel 
  file="c:\downloads\TempReport.xlsx"
  style= seaside 
  options (
   embedded_titles='yes'
   embedded_footnotes='yes'
   Autofilter = "Yes"
   Frozen_headers = "Yes"
   sheet_name='RecStat' 
   orientation='landscape' 
   row_repeat='1-2'
  )
;
 
PROC REPORT DATA=temp nowd SPLIT="*"
   STYLE (SUMMARY) = [COLOR=WHITE BACKGROUNDCOLOR=ALICEBLUE font_weight=bold]
;
  COLUMNS subjectid  cal_date;
 
DEFINE subjectid/display 'Subject' style=[cellwidth= .85 in];
DEFINE cal_date /display format=date11. "Calendar Date"
   style(column)={tagattr="type:Date format:dd-mmm-yyyy;@"}
;

run;
ods excel close ;

[amiller]

Thank you. I just usually output to xml because it looks better. I was closing the ods by the way.

If I change it to ODS Excel instead of ODS tagsets.excelxp and output to xlsx instead of xml as your example shows, it does work. Why does using tagsets mess this up? I think I thought I had to use tagsets because I my output file has 2 worksheets, but this worked.

[amiller]

As I explained below, changing to ODS excel instead of ODS tagsets works and my date field is formatted as a date in excel as I want it. However, when I run the full data (I was limiting to 10 obs while testing), I get an "insufficient memory" error and the report does not run. There are over 37K obs in my data. Does ODS excel have a limit and SAS is having trouble opening this amount of obs in excel? I do not get this error when I use tagsets.

Thanks!

[SASKiwi]

@amiller  - Run this to confirm your current MEMSIZE setting and post the results:

proc options option = memsize;
run;

Increasing MEMSIZE may allow your program to work.

[Ksharp]

1) You could save a lot of memory by closing all the destination:

ods _all_ close;

........start your code here.........

2)Make your sas session have more meomory by specifying option 

-memsize 60G

in your sasv9.cfg

[Patrick]

@amiller 

I can run the sample program @Tom shared earlier in the SAS ODA environment even when creating 37K rows (which is not that much).

Spoiler

data temp;
  do cal_date='01JUN1922'd to '18sep2023'd;
    subjectid+1;
    output;
  end;
run;

ods _all_ close;
ods excel 
  file="&userdir/TempReport.xlsx"
  style= seaside 
  options (
   embedded_titles='yes'
   embedded_footnotes='yes'
   Autofilter = "Yes"
   Frozen_headers = "Yes"
   sheet_name='RecStat' 
   orientation='landscape' 
   row_repeat='1-2'
  )
;
 
PROC REPORT DATA=temp nowd SPLIT="*"
  STYLE (SUMMARY) = [COLOR=WHITE BACKGROUNDCOLOR=ALICEBLUE font_weight=bold];
  COLUMNS subjectid  cal_date;
 
  DEFINE subjectid/display 'Subject' style=[cellwidth= .85 in];
  DEFINE cal_date /display format=date11. "Calendar Date"
    style(column)={tagattr="type:Date format:dd-mmm-yyyy;@"}
    ;
run;
ods excel close ;

What was a bit annoying with the SAS Studio version in the ODA environment was, that I couldn't prevent SAS Studio trying to open the generated HTML report. Make sure you close this output destination before running your Proc Report.

When I run the first time I didn't have ODS _ALL_ CLOSE; in my code. I got below prompt:

I've done a trial where I also kept the HTML destination open and didn't tick "Display warning if..." and things still worked for me with the sample program. But I guess if your report is much bigger/wider then you could hit such an out-of-memory condition. As I understand things this would be a client (browser) side out-of-memory condition where increasing the memsize on the SAS server side wouldn't make a difference.

[amiller]

I am using SAS 9.4

[Tom]

---

@amiller wrote:

I am using SAS 9.4

---

So is almost everyone since the first version of 9.4 came over a decade ago.

https://blogs.sas.com/content/iml/2013/08/02/how-old-is-your-version-of-sas-release-dates-for-sas-so...

data Releases;
format Date DATE7.;
input Category $13. Release $9. Date DATE9. StatRelease $5.;
datalines;
Ancient      8.0      01Nov1999 
Ancient      8.1      01Jul2000 
Ancient      8.2      01Mar2001 
Ancient      9.0      01Oct2002 
Ancient      9.1      01Dec2003 
Ancient      9.1.3    01Aug2004 
Ancient      9.2      01Mar2008 9.2
Ancient      9.2m2    01Apr2010 9.22
Ancient      9.3      12Jul2011 9.3
Ancient      9.3m2    29Aug2012 12.1
Old          9.4      10Jul2013 12.3
Old          9.4m1    15Dec2013 13.1
Old          9.4m2    05Aug2014 13.2
Modern       9.4m3    14Jul2015 14.1
Modern       9.4m4    16Nov2016 14.2
Modern       9.4m5    19Sep2017 14.3
Recent       9.4m6    20Nov2018 15.1
Recent       9.4m7    18Aug2020 15.2
Recent       9.4m8    31Jan2023 15.3
;

Check the value of the automatic macro variable SYSVLONG.

ODS EXCEL works find in even in the 4.5 year old version 9.4M7 of SAS I am using.

299  %put &=sysvlong;
SYSVLONG=9.04.01M7P080520