topic Re: need help with proc Report with preloadfmt(completerows) in ODS and Base Reporting

need help with proc Report with preloadfmt(completerows)

anu — Tue, 19 Feb 2013 18:46:58 GMT

Hi everyone - Hoping to get some tips for using preloadfmt with completerows and exclusive options.

I'm trying to create the below using proc report. I have format defined to get all the columns across but I'm having issues with creating all rows for Subtype.

1. Preloadfmt with completerows gives all possible groupings.

2. Preloadfmt with exclusive options is not displaying all the 3 SubTypes.

					Monday	Tuesday	Wesnesday	Thursday	Friday
Cust	Name	Balance	Type	SubType	Amt	Amt	Amt	Amt	Amt
1234	ABC	xxxxx	One	A
				B
				C
			Two	A
			Two	B

I searched the forum for similiar postings and did not help. Any input is appreciated.

Thanks!

Re: need help with proc Report with preloadfmt(completerows)

Cynthia_sas — Wed, 20 Feb 2013 04:08:50 GMT

Hi:

While it is useful to see the output that you want, it is even more helpful to see what your input data looks like and also, see the code that you've tried. Truthfully, I don't use EXCLUSIVE with PROC REPORT. I generally find that between PRELOADFMT and COMPLETEROWS, COMPLETECOLS I can get everything I've wanted.

There are several options that you might want to consider. Not just COMPLETECOLS (for the ACROSS variables), but also COMPLETEROWS for the row GROUP variables. So, without seeing your code, including your format code and your data, it is hard to speculate on what is happening. For anyone to try to help you, they have to make up some data and they might not pick a structure for their "fake" data that matches the structure of your real data.

I'm not entirely sure you need the EXCLUSIVE option. Have you looked at this Tech Support note? 39648 - How to create a report using PROC REPORT to show all the possible variable values

But, here's an example using SASHELP.CLASS. There are NOT any 17 or 18 year old students in SASHELP.CLASS and the values for the SEX variable are all M or F (there are no U values). You should be able to run this code and examine the differences. Note that COMPLETEROWS makes a big difference to report 1c). If you can't post your data and your code, then I'd recommend that you open a track with Tech Support because there are too many unknowns to do more than guess.

cynthia

proc format;

value agef 11-12 = 'Not Teens'

13-16 = 'Teens'

17-18 = 'Young Adults';

value $gendf 'M' = 'Males'

'F' = 'Females'

'U' = 'Unknown';

run;

options missing = 0;

ods html file='c:\temp\show_completerows.html';

proc report data=sashelp.class nowd completerows;

title '1a) Use Just COMPLETEROWS NO ACROSS';

column sex age height;

define sex / group f=$gendf. preloadfmt;

define age / group preloadfmt f=agef.;

define height / mean 'Avg Height';

run;

proc report data=sashelp.class nowd completerows;

title '1b) Need completerows for $gendf.';

column sex age,height;

define sex / group f=$gendf. preloadfmt;

define age / across preloadfmt f=agef.;

define height / mean 'Avg Height';

run;

proc report data=sashelp.class nowd ;

title '1c) WithOUT completerows for $gendf.';

column sex age,height;

define sex / group f=$gendf. preloadfmt;

define age / across preloadfmt f=agef.;

define height / mean 'Avg Height';

run;

ods html close;

Re: need help with proc Report with preloadfmt(completerows)

anu — Wed, 20 Feb 2013 14:25:32 GMT

Thanks Cynthia. I check the differences in the above code.

Re: need help with proc Report with preloadfmt(completerows)

anu — Wed, 20 Feb 2013 21:41:11 GMT

Here is the format and the proc report code...

f=dates. --> is created using cntlin to include all the days in a given month.

proc format;
value $type 'Type1' = 'A'
'Type2' = 'B'
'Type3' = 'C';
run;

proc format;
value $side 'C'='One'
'D'='Two';
run;

proc report data=final missing nowd spanrows;
options missing=' ';
column (Cust Cust_Name Acct_Bal Side Type) Trxn_Dt, (Flag TRXN_AMT) Total;
define Cust /group 'Cust' style(column)={vjust=m cellwidth=.6in};
define Cust_Name /group 'Name' style(column)={vjust=m cellwidth=2.2in};
define Acct_Bal/group 'Balance' f=dollar20.2 style(column)={vjust=m} ;
define Side /group 'Type' style(column)={cellheight=.7in} f=$side.;
define Type / group 'Sub Type' style(column)={cellheight=.4in} f=$type.;
define Trxn_Dt/ ' ' across style(header)={cellwidth=1.4in} f=dates. preloadfmt order=internal ;
define Flag / display width=25 noprint;
define TRXN_AMT /'Amt' f=dollar12.2;
define Total /computed 'Total' f=dollar20.2 style(column)={cellwidth=1.2in};
compute Total;
Total = sum (_Computes total across_);
endcomp;
run;

Re: need help with proc Report with preloadfmt(completerows)

Cynthia_sas — Wed, 20 Feb 2013 22:21:56 GMT

Hi:

Without a sample of your data (and the code with CNTLIN), nobody can replicate your results. Anyone who is even interested in trying will have to make fake data and a CNTLIN format in order to run your code. If you are using PRELOADFMT, it would be necessary to HAVE that format in order to replicate your results or make any suggestions. Also, your code will not run with this statement:

Total = sum (_Computes total across_);

...and you do NOT show what your ODS destination of choice is....HTML, RTF, PDF??? If you cannot post your real data due to sensitive data, then it would be most helpful if you 1) made some fake data that mimics the structure of your real data (or used a SASHELP dataset to illustrate the issue) and 2) showed ALL your code.

If it is too much to provide this information, then your best resource is to work with Tech Support, because they will look at all your code and all your data, keep it confidential and help you come up with the best solution.

cynthia

Re: need help with proc Report with preloadfmt(completerows)

anu — Thu, 21 Feb 2013 01:50:46 GMT

Thanks Cynthia for your response. I'll reach out to the tech support.

Re: need help with proc Report with preloadfmt(completerows)

anu — Thu, 21 Feb 2013 15:26:45 GMT

Cynthia - I was bale to replicate the issue with the sashelp.prdsale dataset. Last try before i reach out to tech support.

options obs=max;
proc sort data=sashelp.prdsale out=prdsale;
by month;
where prodtype='FURNITURE';
run;

options obs=552;
ods html file="c:\temp\sales.html";
proc report data=prdsale2 nowd spanrows style=sasweb;
where country='U.S.A.' and region='EAST' and division='CONSUMER';
column (country region division Year Month) Product, (Actual Predict);
define country/group;
define region/group;
define division/group;
define year/group;
define month/group order=internal;
define product/across;
define actual/display sum noprint;
define predict/display sum ;
run;
ods html close;

Attached is the output. I want to display all months even if its not in the input dataset. I tried using preloadfmt but had no luck. Any advice as to how I can achieve this?

Re: need help with proc Report with preloadfmt(completerows)

Cynthia_sas — Thu, 21 Feb 2013 18:05:14 GMT

Hi:

Did you try COMPLETEROWS? And, in this example, PRELOADFMT won't make a difference because you have not specified a format for the MONTH variable.

cynthia

Re: need help with proc Report with preloadfmt(completerows)

anu — Tue, 26 Feb 2013 15:08:38 GMT

Cynthia - Yes, i did.

options obs=max;
proc sort data=sashelp.prdsale out=prdsale1;
by month;
run;

options obs=720;
data prdsale;
set prdsale1;
run;

proc freq data=prdsale;
tables prodtype*product/list norow nocol nocum;
run;

proc format;
value $prod 'CHAIR'='Chair'
     'DESK'='Desk'
     'TABLE'='Table'
     'LAMP'='Lamp';
run;

ods listing close;
ods noresults;
ods tagsets.excelxp file="c:\temp\sales1.xls" style=sansprinter;
proc report data=prdsale nowd spanrows style=sasweb completerows;
where country='U.S.A.' and region='EAST' and division='CONSUMER' and prodtype='OFFICE';
column (country region division Prodtype Product) Month, (Actual Predict);
define country/group;
define region/group;
define division/group;
define prodtype/group;
define product/group f=$prod. preloadfmt order=internal;
define month/across order=internal;
define actual/display sum noprint;
define predict/display sum ;
run;
ods tagsets.excelxp close;
ods results;
ods listing;

Here is the output,

Lamp below is not display correctly. its shifted a column. Any inputs as to how i can correct it?

Thanks!

Anu.

					Month
					Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Country	Region	Division	Product type	Product	Predicted Sales	Predicted Sales	Predicted Sales	Predicted Sales	Predicted Sales	Predicted Sales	Predicted Sales	Predicted Sales	Predicted Sales	Predicted Sales	Predicted Sales	Predicted Sales
U.S.A.	EAST	CONSUMER	OFFICE	Chair	$789.00	$988.00	$220.00	$946.00	$986.00	$129.00	$433.00	$492.00	$306.00	$154.00	$206.00	$81.00
				Desk	$619.00	$332.00	$669.00	$487.00	$994.00	$331.00	$739.00	$665.00	$921.00	$862.00	$662.00	$517.00
				Desk	Lamp
				Table	Lamp	$649.00	$838.00	$439.00	$207.00	$804.00	$875.00	$473.00	$845.00	$91.00	$630.00	$185.00	$317.00

Re: need help with proc Report with preloadfmt(completerows)

Cynthia_sas — Tue, 26 Feb 2013 16:52:16 GMT

Hi:

See attached screenshot. I ran a modified version of your code to get more countries. I also changed the code just a bit to only get 3 months. However, when I run the code (whether your code or my code), I do not observe the same behavior. My suggestion would be that you either:

1) check your version of TAGSETS.EXCELXP template and update it and/or

2) open a track with Tech Support.

I ran my code using SAS 9.3, Microsoft Office 2010 and Excelxp version: v1.127, 09/26/2011 . So if you are using the same version of TAGSETS.EXCELXP and still running into issues, then Tech Support would be your best resource.

Code below and screenshot attached. To open a track with Tech Support, fill out the form at this link:

http://support.sas.com/ctx/supportform/createForm

cynthia

proc format;
value $prod 'CHAIR'='Chair'
     'DESK'='Desk'
     'TABLE'='Table'
     'LAMP'='Lamp';
run;

ods listing close;
ods html file='c:\temp\sales1.html' style=sasweb;
ods tagsets.excelxp file="c:\temp\sales1.xls"
    style=sansprinter;
proc report data=sashelp.prdsale nowd spanrows
     style=sasweb completerows;
where /* country='U.S.A.' and region='EAST' and */
      division='CONSUMER' and prodtype='OFFICE'
      and quarter=1;
column (country region division Prodtype Product) Month,(Actual Predict);
define country/group;
define region/group;
define division/group;
define prodtype/group;
define product/group f=$prod. preloadfmt order=internal;
define month/across order=internal;
define actual/display sum noprint;
define predict/display sum ;
run;
ods _all_ close;
ods listing;

Re: need help with proc Report with preloadfmt(completerows)

anu — Tue, 26 Feb 2013 19:35:21 GMT

Thanks Cynthia. I updated the tagsets and its working. I'm running on SAS 9.2 (TS2M3). Thanks for your help.

I'm trying to apply the same logic to the attached code. test1.xls is created without any issues but when I use the completerows options (create test2.xls) to get all the categories for Sub_Type, i get the following error.

Warning: In Event:calculate_rowheight, invalid expression: eval $avail_width $avail_width+($worksheet_width[$i]/$widthfudge)

I need to show blank rows even if there is no data like below.

Type	SubType
1	S1
	S2
	S3
2	S1
	S2
	S3

I've attached the sample dataset - test and ddates2 which is used to create the format for all the completecols across.

Also, will there be a problem with using completecols and completerows together?

Re: need help with proc Report with preloadfmt(completerows)

Cynthia_sas — Wed, 27 Feb 2013 03:44:50 GMT

Hi:

I would recommend that you work with Tech Support on this second issue. That error message seems to be something that is coming from TAGSETS.EXCELXP trying to do calculations. I no longer have SAS 9.2 to use and at this point, I think the best thing to do is get Tech Support involved. I do not observe the same issue when I run your program using SAS 9.3. Except for a missing value message in the log, I get no other messages. I get output in HTML (opening an HTML version of the file in a browser) and in Excel when I open the XML file using Excel 2010.

You should not grasp at completerows/completecols as causing your issue. It's not an issue in Version 9.3 and I don't remember any issues in 9.2 with completerows. Completecols is on by default, so most of the time if you have to fiddle with completecols, it's because you want to turn it off. But at any rate, to me this seems like a version of SAS issue.

2100 ods tagsets.excelxp file="C:\temp\test2.xls" style=sansprinter
2101 options(embedded_titles='yes' embedded_footnotes='no' sheet_interval='none'
2102         sheet_name="123" suppress_bylines='yes' frozen_rowheaders='5') ;
NOTE: Writing TAGSETS.EXCELXP Body file: C:\temp\test2.xls
NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.127, 09/26/2011). Add
options(doc='help') to the ods statement for more information.
2103 options nocenter;
2104 title "123";
2105 proc report data=testf.test missing nowd spanrows completerows ;
2106 options missing=' ';
2107 column (Cust Name Bal Type SubType) Dt, (Flag AM Ct) Total;
2108 define Cust /group 'Cust' style(column)={cellwidth=.6in};
2109 define Name /group 'Name' style(column)={cellwidth=2.2in};
2110 define Bal/group 'Balance' ;
2111 define Type /group 'Type' f=$type.;
2112 define SubType / group 'SubType' f=$subtype. preloadfmt order=internal;
2113 define Dt/ ' ' across style(header)={cellwidth=.8in} f=dates. preloadfmt order=internal ;
2114 define flag / display noprint;
2115 define AM /'Amt'   f=dollar12.2;
2116 define Ct/'Ct' f=comma8.;
2117 define Total /computed 'Total' f=dollar20.2 style(column)={cellwidth=1.2in};
2118
2119 compute Total;
2120 Total = sum
2120! (_c7_,_c10_,_c13_,_c16_,_c19_,_c22_,_c25_,_c28_,_c31_,_c34_,_c37_,_c40_,_c43_,_c46_,_c49_,_
2120! c52_,_c55_,_c58_,_c61_,_c64_,_c67_,_c70_,_c73_,_c76_,_c79_,_c82_,_c85_,_c88_,_c91_,_c94_,_c
2120! 97_,_c100_,_c103_,_c106_,_c109_);
2121 endcomp;
2122 run;

NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      7952 at 1:10
NOTE: There were 20 observations read from the data set TESTF.TEST.
NOTE: PROCEDURE REPORT used (Total process time):
      real time           1:07.11
      cpu time            1:07.08


2123
2124 ods tagsets.excelxp close;

If there are problems with how TAGSETS.EXCELXP is doing calculations (given how many ACROSS variables you have), Tech Support are the best folks to delve into the mysteries of TAGSETS.EXCELXP and especially with SAS 9.2. I would have to chalk this one up to a version difference that is best handled by Tech Support.

cynthia

Re: need help with proc Report with preloadfmt(completerows)

anu — Wed, 27 Feb 2013 13:40:52 GMT

Thanks Cynthia, will reach out to tech support.

Re: need help with proc Report with preloadfmt(completerows)

mravy1 — Mon, 02 Dec 2013 15:02:03 GMT

Hi Anu

Did you get an answer on that error?

Warning : In Event: calculate_rowheight, Invalid Expression: eval $avail_width $avail_width + ($worksheet_widths[$i]/ $widthfudge).

If you got an answer please share.

Thanks

Re: need help with proc Report with preloadfmt(completerows)

anu — Thu, 09 Oct 2014 20:17:21 GMT

Hi Mravy1,

As per Tech Support try the below to avoid the error

The warning message that you are receiving is actually due to the column widths. This is a defect in 9.2 (if you have access to and run this in 9.3 you will not get the warnings). Use the deault column width.

Hope this helps!