BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Bootler
Fluorite | Level 6

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 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ
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

3 REPLIES 3
ballardw
Super User

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
SAS Super FREQ
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
Fluorite | Level 6

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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