topic ODS Excel max number of columns in ODS and Base Reporting

ODS Excel max number of columns

barnardmv — Thu, 24 Oct 2019 19:57:13 GMT

Hi,

I'm using ODS Excel to export a dataset into Excel. The dataset has 30 columns and 2000+ rows. Proc Report displays the dataset properly however, when opening the Excel file not all columns are being displayed. Out of the 30 columns only 25 columns are showing.

Is there a max number of columns that ODS Excel can accommodate? Any workaround to this?

Thanks,

Barns

Re: ODS Excel max number of columns

PaigeMiller — Thu, 24 Oct 2019 17:54:08 GMT

when opening the Excel file not all columns are being displayed

Be specific, or show us.

Also show us the code you used to turn PROC REPORT into Excel.

Re: ODS Excel max number of columns

ballardw — Thu, 24 Oct 2019 17:54:59 GMT

You should show the Proc report code and the method you are sending the results to Excel.

Partially because you say "export a dataset" and "Proc Report displays the dataset". Export and Report are not always the same thing. Also it is possible with ODS Excel options to do all sorts of things to rows and columns that can affect the result.

Re: ODS Excel max number of columns

barnardmv — Thu, 24 Oct 2019 19:10:08 GMT

Hi,

Here's the Proc report that I used.

ods Excel
file="&rpt_location./&report_cadetail."
options (absolute_column_width="20,20,40,20,50,20,20,50,20,20,20,20,20,20,25,25,25,25,25,25,25,25,25,30,30,30,30,30,30,30"
sheet_name='Detail exceptions' frozen_headers = 'on' autofilter = 'all' sheet_interval = 'none'
suppress_bylines='yes' embedded_titles='yes' embedded_footnotes='yes'
);

proc report data = report_ca_other_excp
style(column)=[tagattr="wrap:yes" cellwidth=1000% vjust=top ] ;
options missing='';
title justify= left "CA - Report";
columns _ALL_;
run;

ods Excel close;

Re: ODS Excel max number of columns

barnardmv — Thu, 24 Oct 2019 19:12:28 GMT

Hi Paige,

I have 30 fields from the dataset but in Excel it only shows 25 fileds.

Re: ODS Excel max number of columns

Cynthia_sas — Thu, 24 Oct 2019 19:21:16 GMT

Hi:

I am not observing this behavior. When I use SASHELP.CLASS to make some wide data (43 columns), Excel displays all 43 columns as shown below (greatly zoomed out to see all 43 columns):

Here's the test code I ran:

data widedata (drop=i);
  set sashelp.class;
  array yval y2019-y2040;
  ** make 22 extra columns;
  do i = 1 to 22 by 1;
     if i = 1 then yval(i)=age;
     else yval(i) = sum(yval(i-1) + 1);
  end;
  ** make more columns until have more than 40 in total;
  age_2 = age;
  height_2 = height;
  weight_2 = weight;
  name_2 = catt('2~',name);
  age_3 = age;
  height_3 = height;
  weight_3 = weight;
  name_3 = catt('3~',name);
  age_4 = age;
  height_4 = height;
  weight_4 = weight;
  name_4 = catt('4~',name);
  age_5 = age;
  height_5 = height;
  weight_5 = weight;
  name_5 = catt('5~',name);
run;
  
ods excel file='c:\temp\widedata.xlsx';
proc report data=widedata;
  column name age height weight sex
         y2019-y2040 age_2 height_2 weight_2 name_2 
         age_3 height_3 weight_3 name_3
         age_4 height_4 weight_4 name_4
		 age_5 height_5 weight_5 name_5;
run;
ods excel close;

You should be able to run the code since it uses SASHELP.CLASS. If your test does not show all the columns when you open the file with Excel, then that is an issue to report to Tech Support. My Excel columns range from column A to column AQ -- and as you can see the data in column AQ is the number 5 concatenated to the name, as shown in the column statement. So no columns have been dropped by SAS or Excel.

Cynthia

Re: ODS Excel max number of columns

barnardmv — Thu, 24 Oct 2019 19:48:02 GMT

Hi Cynthia,

I've tried the code you provided and it does show all columns. However, when I tried it with a dataset with 2000+ rows then that's the time some of the fields drop off.

Thanks,

Barns

Re: ODS Excel max number of columns

Cynthia_sas — Thu, 24 Oct 2019 20:29:39 GMT

Hi:

Again, that is not what I observe. I modified my program just a bit to create 2850 total rows or observations and still got my 43 columns in the Excel sheet, as shown below:

Cynthia

Re: ODS Excel max number of columns

PaigeMiller — Thu, 24 Oct 2019 20:48:07 GMT

@barnardmv wrote:

I have 30 fields from the dataset but in Excel it only shows 25 fileds.

Show us so we can see for ourselves what you see.

Re: ODS Excel max number of columns

barnardmv — Fri, 25 Oct 2019 14:36:53 GMT

Cynthia,

What is the release of SAS are you running this?

This is what we have.

%put &sysvlong

9.04.01M3P062415

I've used the tagset.excelpxp to do this as a workaround and I do not have the same issue. All 30 fields are exported in Excel when using ODS tagset.excelxp.

Re: ODS Excel max number of columns

Cynthia_sas — Fri, 25 Oct 2019 15:22:33 GMT

Hi: I am using 9.4 both M5 and M6 on different machines. I no longer have 9.4M3 to test with. Since the issue does NOT exist for me with ODS EXCEL my guess is that IF it is a bug, it was fixed in a later release.

Cynthia