topic Re: Proc Report ods excel content issue in ODS and Base Reporting

Proc Report ods excel content issue

mmilbrant — Thu, 25 Jul 2019 06:06:35 GMT

Hi All,

Could you please check my issue with Proc Report Sas.

I'm using SAS 9.4 Entrprise Guide 7.15 HF7.

I'm trying save report in excel but one column is out of report.

Is some restrictions for max columns in report? I didn't find information about it.

Is not matter what is the column, always last is in AZ Excel.

When I choose less columns in report then everything is ok.

In sas report result look fine.

I attached xlsx file and sas program with code (is nothing special but maybe will be helpful for you to find issue).

Many thanks for your support.

I have one warring for below code: 'WARNING 1-322: Assuming the symbol SUMMARIZE was misspelled as sumarize.'

Here is my code:

filename Report "\\fs01\usersbs\mariusz.milbrant\testOdsrent\rent_sas.xlsx";
ods excel file= Report
options(embedded_titles='yes'frozen_headers='yes');
title "Client Rent &id_klient for the period &rok_p._&miesiac_pod - &rok_k._&miesiac_kdo";
proc report data=work.rent_client spanrows ;
Options missing=0;
col year month net_rofit net_cost profit_cost margin
	net_rofit_worker net_cost_worker profit_cost_worker marigin_worker
	net_rofit_compain_person net_cost_compain_person profit_cost_compain_person	margin_compain_person
	net_rofit_kids	net_cost_kids profit_cost_kids margin_kids
	net_rofit_child net_cost_child profit_cost_child margin_child
	net_rofit_senior net_cost_senior profit_cost_senior	margin_senior;

define year / group style(column)={vjust=center just=center};
define month / group style(column)={vjust=center just=center};

define net_rofit / sum format=comma18.2;
define net_cost /sum format=comma18.2;
define profit_cost / computed format=comma18.2;
define margin / computed format= percentn10.2;

define net_rofit_worker /sum format=comma18.2;
define net_cost_worker /sum format=comma18.2;
define profit_cost_worker /computed format=comma18.2;
define marigin_worker / computed format= percentn10.2;

define net_rofit_compain_person /sum format=comma18.2;
define net_cost_compain_person /sum format=comma18.2;
define profit_cost_compain_person /computed format=comma18.2;
define margin_compain_person / computed format= percentn10.2;

define net_rofit_kids /sum format=comma18.2;
define net_cost_kids /sum format=comma18.2;
define profit_cost_kids /computed format=comma18.2;
define margin_kids / computed format= percentn10.2;

define net_rofit_child /sum format=comma18.2;
define net_cost_child /sum format=comma18.2;
define profit_cost_child /computed format=comma18.2;
define margin_child / computed format= percentn10.2;

define net_rofit_senior /sum format=comma18.2;
define net_cost_senior /sum format=comma18.2;
define profit_cost_senior /computed format=comma18.2;
define margin_senior / computed format= percentn10.2;

rbreak after /sumarize ;
compute profit_cost;
profit_cost=net_rofit.sum-net_cost.sum;
endcomp;
compute margin;
margin=((net_rofit.sum-net_cost.sum) / net_rofit.sum);
endcomp;

compute profit_cost_worker;
profit_cost_worker=net_rofit_worker.sum-net_cost_worker.sum;
endcomp;
compute marigin_worker;
marigin_worker=((net_rofit_worker.sum-net_cost_worker.sum)/net_rofit_worker.sum);
endcomp;

compute profit_cost_compain_person;
profit_cost_compain_person=net_rofit_compain_person.sum-net_cost_compain_person.sum;
endcomp;
compute margin_compain_person;
margin_compain_person=((net_rofit_compain_person.sum-net_cost_compain_person.sum)/net_rofit_compain_person.sum);
endcomp;

compute profit_cost_kids;
profit_cost_kids=net_rofit_kids.sum-net_cost_kids.sum;
endcomp;
compute margin_kids;
margin_kids=((net_rofit_kids.sum-net_cost_kids.sum)/net_rofit_kids.sum);
endcomp;

compute profit_cost_child;;
profit_cost_child=net_rofit_child.sum-net_cost_child.sum;
endcomp;
compute margin_child;
margin_child=((net_rofit_child.sum-net_cost_child.sum)/net_rofit_child.sum);
endcomp;

compute profit_cost_senior;
profit_cost_senior=net_rofit_senior.sum-net_cost_senior.sum;
endcomp;
compute margin_senior;
margin_senior=((net_rofit_senior.sum-net_cost_senior.sum)/net_rofit_senior.sum);
endcomp;

run;
ods excel close;

There is result of xlsx file.

Best Regards Mariusz.

Re: Proc Report ods excel content issue

ballardw — Wed, 24 Jul 2019 15:49:47 GMT

Please post code or log in code boxes on the forum opened using the forum's {I} or "running man" icon.

What did the log show?

Without data we can't tell anything that may have gone wrong, especially will that long of a proc report code.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Proc Report ods excel content issue

mmilbrant — Thu, 25 Jul 2019 06:07:29 GMT

Hi Ballardw,

I attached on my first post code and result.
Many thanks for your replay.

Re: Proc Report ods excel content issue

ballardw — Thu, 25 Jul 2019 17:00:17 GMT

INPUT data is needed.

Re: Proc Report ods excel content issue

mmilbrant — Mon, 29 Jul 2019 10:54:39 GMT

Here you are sashelp table.

filename Report "\\your destination file";
ods excel file= Report
options(embedded_titles='yes'frozen_headers='yes');
title "test sas community";
proc report data=SASHELP.LEUTRAIN spanrows ;
col y x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11 x12 x13 x14 x15 x16 x17 x18 x19 x20 x21 x22 x23 x24 x25 x26 x27;
run;
ods excel close;

Re: Proc Report ods excel content issue

SuzanneDorinski — Thu, 01 Aug 2019 02:39:23 GMT

You might be using an older version of SAS. I remember seeing that issue with ODS Excel several years ago, perhaps in SAS 9.4 maintenance release 1?

Using the test code you provided, the last column of data in the spreadsheet is the value of x27 in column AB.

The screen shot below shows that there aren't any blank columns between x24 and x25.

SAS 9.4 Maintenance Release 6 produces correct output for ODS Excel destination

Re: Proc Report ods excel content issue

DWilson — Fri, 09 Aug 2019 15:14:22 GMT

That sample code works on my SAS system:

SAS 9.4 TS1M3

Using SAS EG with a SAS grid running on a linux server