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 🙂
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.
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.