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;
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.
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.