Full value

[DLROW]

Hi , 

The value 'Grand Total' is displayed as 'Grand To'.  I increased the character length upto 100 but stil it is not working. (I use SAS EG)

Please advise.

Compute  Column1 / character length=25;
IF Upcase(_Break_)='test' THEN DO;
Column1="Total";
END;
EndComp;

RBREAK AFTER/SUMMARIZE STYLE ={BACKGROUND=SILVER FONT_WEIGHT=BOLD};

Compute After;
Column1="Grand Total";
EndComp;

Thank you.

[Cynthia_sas · Accepted solution]

Hi:
Does Column1 already exist in your data table? If so, you can't change the length on the COMPUTE block. Specifying length on a COMPUTE block only works for items that have a usage of COMPUTED, since you don't show any of your data or the rest of your code, it's just a guess what you need to do in this case.

  There is one thing you can fix...this statement:

IF Upcase(_Break_)='test' THEN DO;

is not going to work because you have "test" in lower case. It should be:

IF Upcase(_Break_)='TEST' THEN DO;

  However, here's an example where the variable SEX from SASHELP.CLASS is the BREAK variables, so assigning any value to that column will cause truncatioin. But I need for Sub-total and Grand Total needs to appear on the BREAK lines.

Hope this helps point you in the right direction. Otherwise, you'll need to post ALL of your code and post some sample data for folks to help you in more depth.

Cynthia

[DLROW]

Yes, column1 exist in the data table. I used your method and it worked. Thank you, Cynthia!

[DLROW]

I am just noticing that '0' is populated on the adjacent column of subtotal row. How to get rid off this '0'. Thanks.

Name  sex  age    weight

John    M     40      90

Ellen   M      25      80

      0    subtotal       170

Miranda  F      40    50

Lucy       F      30    60

      0       subtotal 70  110

[Cynthia_sas]

Hi:
Did you run my test code? I do not get 0 populated in the row adjacent to the break row. Even if I put NAME before SEX in my PROC REPORT code, I still get a blank in the name column.

  I do not know why you are getting a 0 in your name column. It is either your data or your code, neither of which has been posted.

Cynthia

[DLROW]

Thanks for getting back on this.

Code :

Proc Report;

columns Year Producttype Product  Product 1

define Producttype / noprint;

define Producttype1 / computed 'Producttype'       

define Product / order noprint;

define Product1 / computed'Product';

break after Year / summarize style={font_Weight=Bold};

break after Product/ summarize style={font_Weight=Bold};

RBREAK AFTER/SUMMARIZE STYLE ={FONT_WEIGHT=BOLD};

Compute Producttype1  / character length=10 ;

 Producttype1 = Producttype;

 IF Upcase(_Break_)=’PRODUCT' THEN DO;

           Producttype1='Sub Total';

           Year="";

           Product="";

     END;

 EndComp;

Compute Product1 / character length=40;

       Product1=Product;

   IF Upcase(_Break_)='YEAR' THEN Product1='Total';

   else if _BREAK_='_RBREAK_' then Product1='Grand Total';

EndComp;    

Attached is the output..         

[DLROW]

Output: 

Year         Product          Product type        Amount

2006       Car                 Toyota                         25000

                                        Honda                         26000

                                        BMW                             50000

0              Car                   subtotal                        1000000                 

                 Furniture            Sofa                       5000

                                            Table                         3000

                                           Chair                           1000

0               Furniture          subtotal                 9000

2006            Total                                                 19000000

2008       Car                 Toyota                         25000

                                        Honda                         26000

                                        BMW                             50000

0              Car                   subtotal                        1000000

                 Furniture            Sofa                       5000

                                            Table                         3000

                                           Chair                           1000

    0               Furniture          subtotal                 9000

   2006            Total                                                 19000000

                        Grand Total                                     38000000  

[Cynthia_sas]

Hi: Here are some notes on your code:
1) Why is your code missing the DEFINE statement for YEAR? You should not be able to do BREAK Processing on YEAR unless it is an ORDER or GROUP item. Also, is YEAR character or numeric?
2) Why is the year 2006 under the 2008 rows on the report?
3) Producttype1 does NOT seem to be in the COLUMN statement.
4) Your PROC REPORT does not have a RUN statement -- is the code you show complete or are there more statements?
5) You do not show your ODS Destination...what destination do you want?? HTML, RTF, PDF???

6) Just noticed, there's no semi-colon at the end of your COLUMN statement.

7) You have Product (space) 1 in the COLUMN statement instead of Product1.

😎 You show Amount on the output, but AMOUNT is NOT in the COLUMN statement.

9) Missing semi-colon on DEFINE statement for Producttype1.

10) Why do you have Year="" ??

11) Why are you putting SubTotal under ProductType1, but putting Total and Grand Total under Product1???

12) You need to post your REAL data...something seems odd in your output. I would expect PRODUCTTYPE to have values like Car and Furniture...but that column is labeled "Product".

Are you sure your code is producing ANY output??

Cynthia

[DLROW]

The one that was sent was sample one. 

1) Why is your code missing the DEFINE statement for YEAR? You should not be able to do BREAK Processing on YEAR unless it is an ORDER or GROUP item. Also, is YEAR character or numeric?

          I missed this in the code that I sent. The code is define Year / group order 'Year'; It is character.
2) Why is the year 2006 under the 2008 rows on the report?

           Sorry, it should be 2008. Typo.
3) Producttype1 does NOT seem to be in the COLUMN statement.

        I missed this in the code that I sent but it is in my original code.

                Columns Year Product Product1 ProductType ProductType1 

                
4) Your PROC REPORT does not have a RUN statement -- is the code you show complete or are there more statements?

                             RUN statement is in my original code. I do have more statements.
5) You do not show your ODS Destination...what destination do you want?? HTML, RTF, PDF???

       Excel 

6) Just noticed, there's no semi-colon at the end of your COLUMN statement.

     /* combining few columns to produce the Column header*/

    Columns Year Product Product1 ProductType ProductType1

 ("Pre &PreDate" Pre_Invoice_amount ("Amount"  pre_ AMount   Pre_Amount_in_Percent) ("Quality" Pre_Sold))

("Post &PostDate" Post_invoice_Amount ("Amount" Post_Amount  Post_Amount_in_percent) ("Quality" Post_Sold))

("Grand Total" combined_Invoice_Amount ("Amount" Combined_Amount  Combined_amount_in _Percent) ("Quality" Combined_Sold));

7) You have Product (space) 1 in the COLUMN statement instead of Product1.

      It is not in actual code.

😎 You show Amount on the output, but AMOUNT is NOT in the COLUMN statement.

       This is to just let you know that the numbers are totaled.

9) Missing semi-colon on DEFINE statement for Producttype1.

           Semicolon is in my original code

10) Why do you have Year="" ??

       so that Year would be empty and would not get values while doing subtotal or total.

11) Why are you putting SubTotal under ProductType1, but putting Total and Grand Total under Product1???

         Requiremnet is Subtotal under Product type : Total under Product and Grand total under Year. I was not able to populate grandtotal under year , so I did it under Product.

12) You need to post your REAL data...something seems odd in your output. I would expect PRODUCTTYPE to have values like Car and Furniture...but that column is labeled "Product".

      It is just false data. 

Are you sure your code is producing ANY output?

I get the output in SAS but when I try to open file in excel it says the file is corrupted.

Code:

ODS EXCEL file = &Outfile.

Proc Report data=Report nowd;

/* combining few columns to produce the Column header*/

    Columns Year Product Product1 ProductType ProductType1

 ("Pre &PreDate" Pre_Invoice _amount("Amount"  pre_ AMount   Pre_Amount_in_Percent) ("Quality" Pre_Sold))

("Post &PostDate" Post_invoice_amount ("Amount" Post_Amount  Post_Amount_in_percent) ("Quality" Post_Sold))

("Grand Total" combined_Invoice_amount ("Amount" Combined_Amount  Combined_amount_in _Percent) ("Quality" Combined_Sold));

define Pre_invoice_amount /'Pre Invoice';

define pre_amount / 'Pre Amount';

define Pre_amount_in_percent / 'Pre Amount Percent';

define Pre_Sold / "Pre Sold";

define Post_invoice_amount /'Post Invoice';

define post_amount / 'Post Amount';

define Post_amount_in_percent / 'Post Amount Percent';

define Post_Sold / 'Post Sold';

define combined_Invoice_amount / 'combined invoice';

define combined_Amount / 'Combined Amount';

define combined_amount_in_ percent /'combined percent';

define Year / group order 'Year';

define Producttype / no print;

define producttype1 / computed 'product';

define Product / order no print;

define Product1 / computed 'Product';

break after Year / summarize style;

break after Product / summarize style;

RBREAK AFTER / Summarize style;

compute producttype1 / character length=10;

producttype1=producttype;

if upcase(_Break_)='PRODUCT' Then do;

   Producttype1='Sub Total';

   Year ="";

   Product = "";

END 

compute Product1 / character length=50;

   Product1=Product;

if upcase(_Break_)='YEAR' Then ENtity1='Total';

 else if _Break_='_RBREAK_' then Entity1='Grand Total';

ENdcomp;

compute Pre_Amount_in_Percent;

  Pre_Amount_in_Percent = pre__amount.sum / pre_invoice_amount;

Endcomp;

compute Post_Amount_in_Percent;

  Post_Amount_in_Percent = post__amount.sum / post_invoice_amount;

Endcomp;

compute combined_Amount_in_Percent;

  combined_Amount_in_Percent = combined__amount.sum / pre_invoice_amount;

Endcomp;

ODS EXCEL CLOSE;

RUN;

Thank you for your time.

[Cynthia_sas]

Hi:
What is Entity1? I see where you create it, however, I don't see where you use ENTITY1.

If YEAR is a Character variable then what is its length? If you do a PROC CONTENTS, is YEAR a character variable with a length of 4?? If so, then the string "Grand Total" will NOT fit in either a length of 4 or a length of 8 and you can't use the simple assignment method if YEAR is actually numeric.

It was great that you posted all your code, mostly without typos. It would have been better if you had posted the original, complete code in the first place, however, without any data, no one can actually RUN your program to actually see the 0 being put into the output or see the issues you're having when you open the file with Excel.

The option is NOPRINT, one word, no spaces. In the code you say is working, you show "no print" with a space in between. Is this really code that works or do you think that the code you posted has more typos?

Also, you only should have 1 usage for YEAR -- either GROUP or ORDER, but not both. Since ORDER appears last, ORDER is used.

Without data it is hard to figure out why your output is corrupted. Typically, I debug the code and make sure the output is the way I want using HTML and then, at the end, when all the headers and formats and numbers are what I want, then I send the output to ODS EXCEL. It simplifies debugging. but again, without any data (just couple of years) or fake data, it is hard to provide constructive criticism on anything except what you've posted....which still shows "no print" -- which should not work. If THAT is your real code, then you should be seeing an error like this:

Cynthia

[Cynthia_sas]

Hi:

  The only way I think you could be getting 0 under the YEAR column would be under these circumstances:

1) YEAR is really numeric not character and

2) you have options missing=0 turned on somewhere in the code.

  For example, consider this fake data:

data fakedata;
length  Year $12 Producttype $10 Product $6 ;
  infile datalines;
  input Year $ Producttype $ Product $ Amount;
  numyear = input(year,4.0);
return;
datalines;
2006 Car Toyota 25000
2006 Car Honda 26000
2006 Car	BMW 50000
2006 Car Tesla .
2006 Furniture Sofa 5000
2006 Furniture Table 3000
2006 Furniture Chair 1000
2008 Car Toyota 25500
2008 Car Honda 26500
2008 Car	BMW 55000
2008 Car Tesla .
2008 Furniture Sofa 5500
2008 Furniture Table 3500
2008 Furniture Chair 1500
;
run;

In this fake data, you can see the the row for Tesla in both years has a . or missing for the Amount value. In the fake data, the variable YEAR is explicitly character, while the variable NUMYEAR is numeric. I can run 2 PROC REPORTS one for the Numeric Year on the left and the other for the Character Year on the right (as shown in the labels) and here's what I can see:

HTML:

ODS EXCEL: 

As you can see, when I use the numeric NUMYEAR variable, I get a 0 in Excel and HTML. Excel does open the XLSX file for me. But when I use the character variable YEAR, I do NOT get a 0 in Excel when the file is opened. Because I set options missing = 0, the row for Tesla correctly shows a 0 for Amount based on the Missing option. But, when I use NUMYEAR, the missing value for YEAR (because you assign a value of "" (quote/quote, no space) -- you are forcing SAS to convert from a character string of missing to a numeric missing, which then gets converted to 0 when the missing option comes into play. On the other hand, when YEAR is character, there are no problems with the assignment statement. In fact, for the "bad" output, you should see this note in the log because of the conversion:

NOTE: Character values have been converted to numeric values at the places given by:
(Line) :  (Column).

Without your data, nobody can run your code. But given that I can duplicate most of your issue with some FAKE data, that leads me to believe that your year variable is numeric and that you have options missing=0; in your code someplace. Also, note that I did not bother to do a Grand Total on the report where I was using NUMYEAR because I know that I can't assign a character string to a numeric variable on a break line.

  Here's the code that produced the "bad" output using the NUMYEAR numeric variable:

ods html path='c:\temp' file='bad_if_year_num.html';
ods excel file='c:\temp\bad_if_year_num.xlsx';
options missing=0;
Proc Report data=fakedata;
columns numYear Producttype Producttype1 Product  Product1 Amount;
define numYear /order 'Numeric Year';
define Producttype / order noprint;
define Producttype1 / computed 'Producttype1';       
define Product / order noprint;
define Product1 / computed'Product1';
define amount / sum f=comma14.;
break after numYear / summarize  style={font_Weight=Bold};
break after Producttype/ summarize style={font_Weight=Bold};
Compute Producttype1  / character length=10 ;
   Producttype1 = Producttype;
EndComp;
Compute Product1 / character length=20;
   Product1=Product;
   IF Upcase(_Break_)='NUMYEAR' THEN do; 
      Producttype1='Total'; 
   end;
   else IF Upcase(_Break_)='PRODUCTTYPE' THEN DO;
           Product1='Sub Total';
		   numyear = '';
   END;
EndComp;  
 compute after numyear;
  line ' ';
endcomp;
run; 
ods html close;
ods excel close;

And, here's the code that produced the "good" output using the YEAR (character) variable:

ods html path='c:\temp' file='good_if_year_char.html';
ods excel file='c:\temp\good_if_year_chars.xlsx';
options missing=0;
Proc Report data=fakedata;
columns Year Producttype Producttype1 Product  Product1 Amount;
define Year /order 'Char Year';
define Producttype / order noprint;
define Producttype1 / computed 'Producttype1';       
define Product / order noprint;
define Product1 / computed'Product1';
define amount / sum f=comma14.;
break after Year / summarize  style={font_Weight=Bold};
break after Producttype/ summarize style={font_Weight=Bold};
rbreak after / summarize  style={font_Weight=Bold};
Compute Producttype1  / character length=10 ;
   Producttype1 = Producttype;
EndComp;
Compute Product1 / character length=20;
   Product1=Product;
   IF Upcase(_Break_)='YEAR' THEN do; 
      Producttype1='Total'; 
   end;
   else IF Upcase(_Break_)='PRODUCTTYPE' THEN DO;
           Product1='Sub Total';
		   Year = ' ';
   END;
  else if _BREAK_='_RBREAK_' then Year='Grand Total';
EndComp;  
compute after Year;
  line ' ';
endcomp;
run; 
ods html close;
ods excel close;

(corrected screen shots and good program)

  I hope this helps point you in a direction. I would recommend getting the program working with ODS HTML first, to make sure you've gotten rid of the 0 problem and then send the output to ODS EXCEL. My guess is that until you fix the issue with the 0, the problem with a corrupt file will persist. If you fix the 0 problem and still get a corrupt file, then that's something you'll have to work on with Tech Support anyway. They would need to look at ALL your code (including macro variables that you are using) and ALL your data (not just 4 or 5 variables) in order to help you come to a resolution to the corrupt file issue, if you still have it after you fix the 0 issue.

Cynthia

[DLROW]

Thank you so much for taking time to work on this. This is a very detailed message and would definitely help people like who are beginners in SAS Reporting. I will go though this and I'm sure this will resolve my issue. Is there any book or website that you recommend to learn beginner and advanced level reporting using options such as ODS and PROC REPORT in generating excel reports in various formats.

Thanks.

[Cynthia_sas]

Hi:
I recommend books by Art Carpenter, Lisa Fine and Jane Eslinger on PROC REPORT. Getting PROC REPORT output to Excel is relatively trivial. You have to understand PROC REPORT first. Then sending the output to Excel will depend on whether you're using ODS HTML and opening the HTML File with Excel; using ODS CSV and opening the CSV file with Excel; or using ODS EXCEL and opening the XLSX file with Excel. Most of the Excel-specific things you need to do are in the suboptions, which are well documented and not specific to PROC REPORT; or have to do with STYLE overrides, which is specific to PROC REPORT.

Cynthia

[DLROW]

Thank you, I have ordered Art Carpenter book. Hope this would help me to learn formatting the reports.

[DLROW]

Thank  you, Cynthia. I worked through as you said here and my report looks perfect. 

I have used following options in my code and I eliminated these,

Missing=0 and 

Missing=''

I also removed  the following which was used in the code:

Year="";

 Product="";

Compute Producttype1  / character length=10 ;

 Producttype1 = Producttype;

 IF Upcase(_Break_)=’PRODUCT' THEN DO;

           Producttype1='Sub Total';

          / * Year="";

           Product=""; */

     END;

 EndComp;

Thanks again.