DATA Step, Macro, Functions and more

Apply formats to specific rows of a dataset?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Apply formats to specific rows of a dataset?

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 Smiley Happy


Accepted Solutions
Solution
‎02-07-2017 11:06 AM
SAS Super FREQ
Posts: 8,868

Re: Apply formats to specific rows of a dataset?

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

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Apply formats to specific rows of a dataset?

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.

Solution
‎02-07-2017 11:06 AM
SAS Super FREQ
Posts: 8,868

Re: Apply formats to specific rows of a dataset?

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
Occasional Contributor
Posts: 8

Re: Apply formats to specific rows of a dataset?

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 206 views
  • 2 likes
  • 3 in conversation