BookmarkSubscribeRSS Feed
anu
Fluorite | Level 6 anu
Fluorite | Level 6

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.

MondayTuesdayWesnesdayThursdayFriday
CustNameBalanceTypeSubTypeAmtAmtAmtAmtAmt
1234ABCxxxxxOneA
B
C
TwoA
B

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

Thanks!

14 REPLIES 14
Cynthia_sas
SAS Super FREQ

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;

anu
Fluorite | Level 6 anu
Fluorite | Level 6

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

anu
Fluorite | Level 6 anu
Fluorite | Level 6

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;

Cynthia_sas
SAS Super FREQ


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

anu
Fluorite | Level 6 anu
Fluorite | Level 6

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

anu
Fluorite | Level 6 anu
Fluorite | Level 6

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?

Cynthia_sas
SAS Super FREQ

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

anu
Fluorite | Level 6 anu
Fluorite | Level 6

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
JanFebMarAprMayJunJulAugSepOctNovDec
CountryRegionDivisionProduct typeProductPredicted SalesPredicted SalesPredicted SalesPredicted SalesPredicted SalesPredicted SalesPredicted SalesPredicted SalesPredicted SalesPredicted SalesPredicted SalesPredicted Sales
U.S.A.EASTCONSUMEROFFICEChair$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
Lamp
Table$649.00 $838.00 $439.00 $207.00 $804.00 $875.00 $473.00 $845.00 $91.00 $630.00 $185.00 $317.00
Cynthia_sas
SAS Super FREQ


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;

anu
Fluorite | Level 6 anu
Fluorite | Level 6

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.

TypeSubType
1S1
S2
S3
2S1
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?

Cynthia_sas
SAS Super FREQ

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

anu
Fluorite | Level 6 anu
Fluorite | Level 6

Thanks Cynthia, will reach out to tech support.

mravy1
Calcite | Level 5

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

anu
Fluorite | Level 6 anu
Fluorite | Level 6

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!


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 4052 views
  • 6 likes
  • 3 in conversation