Apply formats to specific rows of a dataset?

[Bootler]

This is my data. Which breaks up a set of 22 students, seperates them by Grad/Undergrad and into different ethnicities.

Obs	level	total	F	WH	BL	HIS	AS	AN	HP	MULT	UNK	N	R
1	G	9	0.2727	0.04545	0.09091	0	0.09091	0.13636	0	0	0	0.00000	0.04545
2	UG	13	0.2727	0.04545	0.13636	0	0.13636	0.13636	0	0	0	0.04545	0.09091
3	Total	22	12.0000	2.00000	5.00000	0	5.00000	6.00000	0	0	0	1.00000	3.00000

I am trying to apply the format percentw.d to the first two rows only to get the desired result.

Obs	level	total	F	WH	BL	HIS	AS	AN	HP	MULT	UNK	N	R
1	G	9	27%	5%	9%	0%	9%	14%	0%	0%	0%	0%	5%
2	UG	13	27%	5%	14%	0%	14%	14%	0%	0%	0%	5%	9%
3	Total	22	12	2	5	0	5	6	0	0	0	1	3

I tried using

proc print data=sample;

       format F percent9.0 WH percent9.0 BL percent9.0 HIS percent9.0

                  AS percent9.0 AN percent9.0 HP percent9.0 MULT percent9.0
                  UNK percent9.0 N pct. R percent9.0;

run;

but I would get

Obs	level	total	F	WH	BL	HIS	AS	AN	HP	MULT	UNK	N	R
1	G	9	27%	5%	9%	0%	9%	14%	0%	0%	0%	0%	5%
2	UG	13	27%	5%	14%	0%	14%	14%	0%	0%	0%	5%	9%
3	Total	22	1200%	200%	500%	0%	500%	600%	0%	0%	0%	100%	300%

I also tried using

proc format;
value pct
low -< 1 = [percent9.0]
other = [9.0];
run;

proc print data=sample;
format F pct. WH pct. BL pct. HIS pct.
       AS pct. AN pct. HP pct. MULT pct.
       UNK pct. N pct. R pct.;
run;

and I would get

Obs	level	total	F	WH	BL	HIS	AS	AN	HP	MULT	UNK	N	R
1	G	9	27%	5%	9%	0%	9%	14%	0%	0%	0%	0%	5%
2	UG	13	27%	5%	14%	0%	14%	14%	0%	0%	0%	5%	9%
3	Total	22	12	2	5	0%	5	6	0%	0%	0%	1	3

I was wondering if anyone had some helpful tricks.

Much appreciated 🙂

[ballardw]

You might apply the idea of special missing instead of 0.

Such as for the 0 values for the two first rows do something like:

If Level ne 'Total' then do;

    if HIS = 0 then HIS=.D;

End;

Else if Level = 'Total' then do;

   if HIS=0 then HIS=.T;

End;

Use an array to go over the list of variables to set the special missings.

Then the format would look like:

value pct

.D = '0%'

.T = '0'
low -< 1 = [percent9.0]
other = [9.0];
run;

Since special missing behave a tad different in different places I would do this immediately before creating the display.

[Cynthia_sas · Accepted solution]

Hi:
PROC REPORT allows you to define a different format for the numeric variables on some rows and not for other rows. TABULATE does too, but usually, when you allow it to calculate the TOTAL, which does not apply in your case. So, instead, I'd recommend using PROC REPORT for this report and investigating the use of CALL DEFINE to change the format for the first 2 rows.

cynthia

[Bootler]

Thanks I used the following and it worked!

Proc report data=sample;
column level total F WH BL HIS AS AN HP MULT UNK N R;
compute R;
if Level="G" or Level="UG" then do;
    do i = 3 to 13;
         call define(i,'format','percent9.0');
    end;

end;
endcomp;
run;