Accepted Solutions
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
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
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
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
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
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.
-
2765 views
-
1 like
-
-
