Compute Block with Proc Format

[saspert]

Hello,
Inside the Proc Report, I am trying to use a compute block to mask some of the values. I need all the percentages that are less than 10% to say 'Less than 10%'
while the rest of them to have the original value (say 23%, 34.78%....)

proc format;
value tgt_pct_mask 0-10%='Less than 10%'
10%-100%='More than 10%' ;
run;


PROC REPORT DATA=OVERVIEW_OP;
COLUMN DESC TOT_COMP REG BIO_SCREEN MAIL HEALTH;
DEFINE DESC / DISPLAY "Segment";
DEFINE TOT_COMP/ DISPLAY "Completed Program";
DEFINE REG / DISPLAY "Registered ";
DEFINE BIO_SCREEN / DISPLAY "Screening ";
DEFINE MAIL / DISPLAY "Mail Form ";
DEFINE HEALTH_/ DISPLAY "Health ";
compute TOT_COMP;
if TOT_COMP<0.1 then
do;
call define(_col_,'format',tgt_pct_mask);
end;
endcomp;
quit;
run;

But I keep getting this error - ERROR: Value for FORMAT has wrong type in CALL DEFINE. And can anyone confirm the usage of percentages in a proc format (Is that correct)? Also, if I need to format all the columns, where does the do loop go?

I found this note(Sample 38776:) but it does not seem to fit my requirement exactly, because I need it for all the columns but to work only for values that are less than 10%.

Does anyone have suggestions?

Thanks,
saspert

[AndyJ]

Can you try a nested format?

proc format;
value tgt_pct_mask
0-<.1='Less than 10%'
.1-1=[percent7.1];
run;


data sample_data;

do i=1 to 100;
tot_comp=ranuni(1);
output;
end;

run;

proc report data=sample_data nowd;
column i tot_comp;
define i/display;
define tot_comp/analysis format=tgt_pct_mask.;

run;

[AndyJ]

proc format;
value tgt_pct_mask
0-.09999='Less than 10%'
.1-1=[percent7.1];
run;


data sample_data;

do i=1 to 100;
tot_comp=ranuni(1);
output;
end;

run;

proc report data=sample_data nowd;
column i tot_comp;
define i/display;
define tot_comp/analysis format=tgt_pct_mask.;

run;

[saspert]

Wow! Thanks Andy. That worked correctly. The solution is so simple. Wonder why I couldnt think of it. 🙂
Last question - do you know why we are using square brackets [] for the percent7.2?
Thanks,
saspert

Updated message -
Getting 2 different results
Result1 - Correct
Entire Population Less than 10% Less than 10% Less than 10% Less than 10% Less than 10%

Result2 - Incorrect
0 0 0 0 0
Headquarters 100% 0 100% 100% 66.7%
Retail Store 50.0% 25.0% 75.0% 75.0% 50.0% Updated Thursday 03/10


Message was edited by: saspert

[Cynthia_sas]

Hi:
I see several issues. First, Is your variable xxx character or numeric?? Generally, SAS numeric variables do not contain a '%' sign, while character variables could contain a '%' sign.

Second, when I try your PROC FORMAT, I find problems in the LOG:
[pre]
993 proc format;
994 value tgt_pct_mask 0-10%='Less than 10%'
-
22
200
ERROR 22-322: Syntax error, expecting one of the following: ',', =.
ERROR 200-322: The symbol is not recognized and will be ignored.
995 10%-100%='More than 10%' ;
-
22
76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string,
a numeric constant, a datetime constant, a missing value, ;, LOW, OTHER.

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: The previous statement has been deleted.
996 run;
[/pre]

So I'm not sure that the error message from PROC REPORT is relevant until the format issue is cleared up.

Third, the syntax for using a format in PROC REPORT is incorrect. See the code below. The PCT variable was created by using SASHELP.CLASS and dividing height by weight (and then multiplying by 100). This resulted in the percent for Joyce being over 100 -- note how, in the output, the range not included in the format shows just the number. But then, I used a CALL DEFINE to limit the number of decimal places.

cynthia
[pre]
proc format;
value tgt_pct_mask
0-<60='Less than 60%'
60-100='More than 60%' ;

value agec 11-12 = 'Are Youngest'
13-14 = 'Can Babysit'
15-16 = 'Might Drive';

value $gend 'F' = 'Female'
'M' = 'Male';
run;

data calcpct;
set sashelp.class;
pct = (height / weight) * 100;
run;


proc report data=calcpct nowd;
title 'Specify Formats Different Ways';
column age=agecat age name sex height weight pct;
define agecat / order f=agec.;
define name / order;
define age / order;
define sex /display 'Gender' f=$gend.;
define height / display;
define weight / display;
define pct / display f=tgt_pct_mask.;
compute pct;
if pct gt 100 then call define(_col_,'format','6.2');
endcomp;
compute height;
if height le 59.99 then call define(_col_,'format','4.0');
else if height gt 60.00 then call define(_col_,'format','4.1');
endcomp;
run;
[/pre]

Listing Window Output:
[pre]
Age Age Name Gender Height Weight pct
Are Youngest 11 Joyce Female 51 50.5 101.58
Thomas Male 58 85 More than 60%
12 James Male 57 83 More than 60%
Jane Female 60 84.5 More than 60%
John Male 59 99.5 Less than 60%
Louise Female 56 77 More than 60%
Robert Male 64.8 128 Less than 60%
Can Babysit 13 Alice Female 57 84 More than 60%
Barbara Female 65.3 98 More than 60%
Jeffrey Male 62.5 84 More than 60%
14 Alfred Male 69.0 112.5 More than 60%
Carol Female 62.8 102.5 More than 60%
Henry Male 63.5 102.5 More than 60%
Judy Female 64.3 90 More than 60%
Might Drive 15 Janet Female 62.5 112.5 Less than 60%
Mary Female 66.5 112 Less than 60%
Ronald Male 67.0 133 Less than 60%
William Male 66.5 112 Less than 60%
16 Philip Male 72.0 150 Less than 60%
[/pre]

[saspert]

Thank you Cynthia. My proc format was using incorrect input formats. I was not sure if it would take in % values as input. Thats why I was asking about it in my earlier post.
My variables are numeric but I need to format it based on the condition if it is less than 10% (or 0.1).

Now in your proc format, is it possible to use it as
proc format;
value tgt_pct_mask 0-60='Less than 60%'
60-100=[percent6.2];
run;

like Andy's code above.

Andy,
I used your suggestion and it seems to work only some times. Not sure why. When I have values as 0 and 1, it gives back 0 and 100.0%. It needs to be 'Less than 10%' and 100.00%.

Thanks,
saspert.

[Cynthia_sas]

Hi:
I have to say that the answer is "it depends" -- what does your data look like?? Andy's solution assumed that your numbers were already fractions and not multiplied by 100 (while your recent question shows the numbers as being multiplied by 100). There is a difference between:
[pre]
Andy:
proc format;
value tgt_pct_mask
0-.09999='Less than 10%'
.1-1=[percent7.1];
run;

Your question:
proc format;
value tgt_pct_mask 0-60='Less than 60%'
60-100=[percent6.2];
run;
[/pre]

While in my solution, the numbers WERE already multiplied by 100, so using the PERCENT format would not be appropriate. The PERCENT format does a multiply by 100. So without knowing what your data looks like, I can only say that you would NOT want to apply a PERCENT format to my example. From the doc:
"The PERCENTw.d format multiplies values by 100, formats them the same as the BESTw.d format, and adds a percent sign (%) to the end of the formatted value, while it encloses negative values in parentheses. "
http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000205182.htm

Also, your width should be larger than 6.2, because (as it explains in the doc):
"The width of the output field must account for the percent sign (% ) and parentheses for negative numbers, whether the number is negative or positive."

At any rate, if you run the program below, I think you will see that it makes a BIG difference whether your number has already been multiplied by 100 or not.

cynthia
[pre]
proc format;
value tgt_pct_mask
0-<60='Less than 60%'
60 - high =[percent9.2];

value other low-<.6 = 'Less than 60%'
.6 - high =[percent9.2];

value agec 11-12 = 'Are Youngest'
13-14 = 'Can Babysit'
15-16 = 'Might Drive';

value $gend 'F' = 'Female'
'M' = 'Male';
run;

data calcpct;
set sashelp.class;
** PCT is already multiplied by 100;
pct = (height / weight) * 100;

** PCT2 is NOT multiplied by 100;
pct2 = (height/weight);
unfmt = pct2;
run;


ods listing;
proc report data=calcpct nowd ls=150 nocenter;
title 'Specify Formats Different Ways';
column age=agecat age name sex height weight pct pct2 unfmt;
define agecat / order f=agec.;
define name / order;
define age / order;
define sex /display 'Gender' f=$gend.;
define height / display;
define weight / display;
define pct / display f=tgt_pct_mask. 'PCT/Mult by 100/WRONG';
define pct2 / display f=other. 'PCT2 is fraction';
define unfmt / display 'No Format/Internal Value/for PCT2';
compute pct;
if pct gt 100 then call define(_col_,'format','6.2');
endcomp;
compute height;
if height le 59.99 then call define(_col_,'format','4.0');
else if height gt 60.00 then call define(_col_,'format','4.1');
endcomp;
run;
[/pre]

[saspert]

Great. Thank you Cynthia. I talked to the business user and we decided to leave the variables as fractions and not convert them to percentw.d format. We will format them while developing the proc report.

It seems to work for all values except 0.

0 0 0 0 0
Headquarters 100.00% 0 100.00% 100.00% 66.67%
Retail Store 50.00% 25.00% 75.00% 75.00% 50.00%

Shouldnt 0 be 'Less than 10%'. My code is here.

PROC FORMAT;
VALUE TGT_PCT_MASK low-@0.1='Less than 10%'
0.1-1=[percent9.2] ;
RUN;

I put @ instead of the lesser than symbol.

Thanks,
saspert

[Cynthia_sas]

Hi:
I don't quite understand what your report row of all 0 0 0 0 0 means, since there is no identifying text to the left of the zeroes.

But in my example, as you can see, 0 is indeed being formatted by my format.

cynthia

Revised code to put 0 in the data:
[pre]
proc format;
value other low-<.6 = 'Less than 60%'
.6 - high =[percent9.2];

value agec 11-12 = 'Are Youngest'
13-14 = 'Can Babysit'
15-16 = 'Might Drive';

value $gend 'F' = 'Female'
'M' = 'Male';
run;

data calcpct;
set sashelp.class;
** PCT2 is NOT multiplied by 100;
pct2 = (height/weight);
unfmt = pct2;
output;
if _n_ = 1 then do;
name = 'Aaaaaa';
age = 11;
sex = 'F';
height = 0.00;
weight = 0.00;
pct = 0;
pct2 = 0;
unfmt = pct2;
output;
end;
run;

ods listing;
proc report data=calcpct nowd ls=150 nocenter;
where age = 11;
title 'Specify Formats Different Ways';
column age=agecat age name sex height weight pct2 unfmt;
define agecat / order f=agec.;
define name / order;
define age / order;
define sex /display 'Gender' f=$gend.;
define height / display;
define weight / display;
define pct2 / display f=other. 'PCT2 is fraction';
define unfmt / display 'No Format/Internal Value/for PCT2';
compute height;
if height le 59.99 then call define(_col_,'format','4.0');
else if height gt 60.00 then call define(_col_,'format','4.1');
endcomp;
run;
[/pre]

Output for Age 11 only:
[pre]
No Format
Internal
PCT2 is Value
Age Age Name Gender Height Weight fraction for PCT2
Are Youngest 11 Aaaaaa Female 0 0 Less than 60% 0
Joyce Female 51 50.5 101.58% 1.0158416
Thomas Male 58 85 67.65% 0.6764706
[/pre]

[saspert]

Thanks Cynthia. When I try your code it works correctly.
I was able to resolve the issue. Message was edited by: saspert

[saspert]

Hi Cynthia,
I have a related issue with this now. I am using the proc format and format=tgt_pct_mask in this way. I am noticing that because of the mix of character and numeric output, all the percentages are aligned to the right and all the characters are aligned to the left.

Column
12.23%
55.22%
Less than 10%
84.12%

How do I align them all to the right? I think the above example does not seem to align the way my output is.

Thanks,
saspert

[Cynthia_sas]

Hi:
I'm not exactly sure. My guess would be that you are using "vanilla" ODS HTML -- which creates HTML 4.0 compliant tags. And it was on text-alignment and style issues that Microsoft differed with the W3C over HTML 4.0 -- which is why they came up with their own "flavor" of HTML (MSHTML). ODS MSOFFICE2K create Microsoft "flavor" of HTML and ODS TAGSETS.EXCELXP creates Microsoft "flavor" of Spreadsheet Markup Office 2003 XML.

When I use the code below in regular SAS, not in a stored process, the MSOFFICE2K and TAGSETS.EXCELXP output both show the PCT2 column as being right justified, even after I open the output in Excel. However, with the "vanilla" ODS HTML output, the character string created by the format is left-justified when Excel opens the file.

My guess is that either 1) you need to change the flavor of ODS destination you use or 2) the client application that opens the stored process output is ignoring the default justification (using ODS MSOFFICE2K with Excel as the client app is a simple override to &_ODSDEST). And, as I've said before, not all client applications can receive ODS HTML -- so, for example, if the text alignment doesn't work in Web Report Studio or PowerPoint, that would be an issue for you to raise with Tech Support.

cynthia

Code to try in Base SAS (based on previous data):
[pre]
proc format;
value other low-<.6 = 'Less than 60%'
.6 - high =[percent9.2];

value agec 11-12 = 'Are Youngest'
13-14 = 'Can Babysit'
15-16 = 'Might Drive';

value $gend 'F' = 'Female'
'M' = 'Male';
run;

data calcpct;
set sashelp.class;
** PCT2 is NOT multiplied by 100;
pct2 = (height/weight);
unfmt = pct2;
output;
if _n_ = 1 then do;
name = 'Aaaaaa';
age = 11;
sex = 'F';
height = 0.00;
weight = 0.00;
pct = 0;
pct2 = 0;
unfmt = pct2;
output;
end;
run;

ods listing close;
ods html file='c:\temp\calcpct_ht4.xls' style=sasweb;
ods msoffice2k file='c:\temp\calcpct_mso.xls' style=sasweb;
ods tagsets.excelxp file='c:\temp\calcpct_xp.xls' style=sasweb;
proc report data=calcpct nowd ls=150 nocenter;
where age = 11;
title 'Specify Formats Different Ways';
column age=agecat age name sex height weight pct2 unfmt;
define agecat / order f=agec.;
define name / order;
define age / order;
define sex /display 'Gender' f=$gend.;
define height / display;
define weight / display;
define pct2 / display f=other. 'PCT2 is fraction' style(column)={cellwidth=1.5in};
define unfmt / display 'No Format/Internal Value/for PCT2';
compute height;
if height le 59.99 then call define(_col_,'format','4.0');
else if height gt 60.00 then call define(_col_,'format','4.1');
endcomp;
run;
ods _all_ close;
[/pre]

[saspert]

That is interesting. Thank you Cynthia. I will try your code when I get a chance.