topic problems applying format on subtotals through proc report in SAS Enterprise Guide

problems applying format on subtotals through proc report

deleted_user — Wed, 23 Jan 2008 05:29:14 GMT

hi,

i have a problem applying format on average land possessed (hectare 0.00) through proc report below is my piece of code
i have to apply format only to the row of average land possessed (hectare 0.00)
bcoz i am using the common variable to calculate average land as well as other colmuns.iwant format 8.2 only on the above row and others 8.

code:

proc format ;
value dottozero .='0' other = [8.];
run;
proc sql noprint;
select sum(Multiplier) into:all2 from Rural;
select sum(multiplier) into:a1-:a5 from Rural
group by Soc_Grp_Name;
select count(hh_info_Skey) into:s1-:s5 from Rural
group by Soc_Grp_Name;
quit;
options orientation=landscape;
Proc Report data=WORK.Rural out=temp;
column
cnt1
cnt
cnt2
cnt_ms
sector_cd
(Multiplier=all3)
('size class of land possessed (hectares)' Land_Gp_Desc1)
('household social group' Soc_Grp_Name,(Multiplier Multiplier=all1))
('all' Multiplier=all)
("household" ""("estd.(00)" Multiplier=all_estpersons)("sample" hh_info_Skey=all_samper) SIZE macrovar macrompcecal);
define sector_cd / noprint;
define cnt_ms / noprint group ;
define cnt2 / noprint group ;
define cnt / noprint group ;
define cnt1 / noprint group ;
define Land_Gp_Desc1 / group "" order=internal ;
define Soc_Grp_Name / across "" order=data;
define Multiplier / sum "" format=dottozero.;
define all_estpersons / sum "" format=8.;
DEFINE SIZE / COMPUTED NOPRINT;
define all / sum "" format=dottozero.;
DEFINE ALL1/ sum "" noprint format=8.2;
DEFINE ALL2/sum "" NOPRINT;
DEFINE ALL3/mean "" noprint;
define all_samper / n "" ;
define macrovar / computed noprint;
define macrompcecal / computed noprint;
break after cnt1/summarize;
break after cnt/summarize;
break after cnt2/summarize;
break after cnt_ms/summarize;
COMPUTE SIZE;
_C8_ = _C8_ /&a5 *1000;
_C10_ = _C10_ /&a4 *1000;
_C12_ = _C12_ /&a2 *1000;
_C14_ = _C14_ /&a3*1000;
_C16_ = _C16_ /&a1*1000;
all_estpersons=all_estpersons/100;
ALL=ALL/&ALL2*1000;
ENDCOMP;

compute after cnt_ms;
Land_Gp_Desc1='total';
_C8_ = _C8_;
_C10_ = _C10_;
_C12_ = _C12_;
_C14_ = _C14_;
_C16_ = _C16_;
endcomp;

compute after cnt2;
Land_Gp_Desc1='per 1000 dist. of hhs.';
_C8_ = &a5/&all2*1000;
_C10_ = &a4/&all2*1000;
_C12_ = &a2/&all2*1000;
_C14_ = &a3/&all2*1000;
_C16_ = &a1/&all2*1000;
endcomp;

compute after cnt;
Land_Gp_Desc1='average land possessed (hectare 0.00)';
_C8_ = &a5/&s5;
_C10_ = &a4/&s4;
_C12_ = &a2/&s2;
_C14_ = &a3/&s3;
_C16_ = put(&a1/&s1,8.2);
all=SUM(_C8_,_C10_,_C12_,_C14_,_C16_);
all3=.;
all_estpersons=.;
all_samper=.;
endcomp;

compute after cnt1;
Land_Gp_Desc1='estd. hhs.(00)';
_C8_ = &a5/100;
_C10_ = &a4/100;
_C12_ = &a2/100;
_C14_ =&a3 /100;
_C16_ = &a1/100;
all=SUM(_C8_,_C10_,_C12_,_C14_,_C16_);
all3=.;
all_estpersons=.;
all_samper=.;
endcomp;

compute after;
Land_Gp_Desc1='sample hhs';
_c8_=&s5;
_c10_=&s4;
_c12_=&s2;
_c14_=&s3;
_c16_=&s1;
all=SUM(_C8_,_C10_,_C12_,_C14_,_C16_);
all3=.;
all_estpersons=.;
all_samper=.;
endcomp;
rbreak after / summarize ;
run;

end of code.

so tell me some solution to do these.

Re: problems applying format on subtotals through proc report

Cynthia_sas — Wed, 23 Jan 2008 07:00:05 GMT

Hi:
Formats can be applied for a break line using a CALL DEFINE statement, as shown in the following program.

When you run PROC REPORT with BREAK and RBREAK statements, PROC REPORT creates an internal variable named _BREAK_ that contains a value which tells you whether the current line was generated by a BREAK or RBREAK statement. If the line is based on break statement, then the value of _BREAK_ is the name of the group variable; if the line is based on an RBREAK statement, then the value of _BREAK_ is _RBREAK_; if the report line does not come from a BREAK or RBREAK statement being executed, then the value of _BREAK_ is blank.

For more help on CALL DEFINE or the use of _BREAK_ or _RBREAK_, consult the PROC REPORT documentation or possibly contact Tech Support for more help.

cynthia
[pre]
ods listing close;

data shoes;
set sashelp.shoes;
brk1 = 1;
brk2 = 2;
run;

ods html file='c:\temp\call_def_format.html' style=sasweb;
proc report data=shoes nowd;
where region in ('Asia', 'Canada') and
product contains ('Dress');
column brk1 brk2 product region,(sales sales=sl_n);
** set brk1 and brk2 to NOPRINT after you understand;
** how they are used to change the format;
define brk1 / group '_c1_';
define brk2 / group '_c2_';
define product /group '_c3_';
define region / across 'Region/_c4_, _c5_, _c6_, _c7_';
define sales/sum f=dollar15.;
define sl_n/n f=comma15. 'N';
break after brk1 / summarize;
break after brk2 / summarize;
rbreak after / summarize;
compute sl_n;
if upcase(_break_) = 'BRK1' then do;
call define('_c4_', 'format', 'dollar15.2');
call define('_c5_', 'format', 'comma15.2');
call define('_c6_', 'format', 'dollar15.2');
call define('_c7_', 'format', 'comma15.2');
end;
else if upcase(_break_) = 'BRK2' then do;
call define('_c4_', 'format', 'dollar15.3');
call define('_c5_', 'format', 'comma15.4');
call define('_c6_', 'format', 'dollar15.3');
call define('_c7_', 'format', 'comma15.4');
end;
else if _break_ = '_RBREAK_' then do;
call define('_c4_', 'format', 'dollar15.1');
call define('_c5_', 'format', 'comma15.1');
call define('_c6_', 'format', 'dollar15.1');
call define('_c7_', 'format', 'comma15.1');
end;
endcomp;
compute after brk2;
product = 'brk2';
endcomp;
compute after brk1;
product = 'brk1';
endcomp;
compute after;
product = '_RBREAK_';
endcomp;
run;
ods html close;
[/pre]

Re: problems applying format on subtotals through proc report

deleted_user — Wed, 23 Jan 2008 08:36:05 GMT

thanks.