I am using the SAShelp cars data set here. I know format (format=8.0) could be used to round to the nearest whole number, but I would like to round to a multiple. Is this possible with Proc Report?
This is what I have:
DATA MSRP_cars_rounded;
SET sashelp.cars;
Count = 1;
RUN;
PROC REPORT DATA = MSRP_cars_rounded;
COLUMN Origin DriveTrain Count ('MSRP' MSRP=MSRP_Med MSRP=MSRP_Q1 MSRP=MSRP_Q3);
DEFINE Origin / group;
DEFINE DriveTrain / group;
DEFINE MSRP_Med / analysis median 'Median';
DEFINE MSRP_Q1 / analysis Q1 'Q1';
DEFINE MSRP_Q3 / analysis Q3 'Q3';
RUN;
MSRP | |||||
Origin | DriveTrain | Count | Median | Q1 | Q3 |
Asia | All | 34 | $26,898 | $21,087 | $32,445 |
Front | 99 | $19,560 | $15,030 | $25,920 | |
Rear | 25 | $31,045 | $25,700 | $41,010 | |
Europe | All | 36 | $39,445 | $34,813 | $49,390 |
Front | 37 | $34,845 | $23,955 | $40,565 | |
Rear | 50 | $52,243 | $39,995 | $74,320 | |
USA | All | 22 | $32,448 | $25,520 | $41,465 |
Front | 90 | $23,115 | $18,995 | $28,790 | |
Rear | 35 | $30,835 | $22,010 | $41,815 |
This is what I want:
MSRP | |||||
Origin | DriveTrain | Count | Median | Q1 | Q3 |
Asia | All | 35 | $26,900 | $21,100 | $32,400 |
Front | 100 | $19,550 | $15,050 | $25,900 | |
Rear | 25 | $31,000 | $25,700 | $41,000 | |
Europe | All | 35 | $39,400 | $34,800 | $49,400 |
Front | 35 | $34,800 | $24,000 | $40,600 | |
Rear | 50 | $52,200 | $40,000 | $74,300 | |
USA | All | 20 | $32,400 | $25,500 | $41,500 |
Front | 90 | $23,100 | $19,000 | $28,800 | |
Rear | 35 | $30,800 | $22,000 | $41,800 |
Thanks,
Even if PROC REPORT could do that it is probably going to be a lot better to just do the calculations yourself.
proc means data=sashelp.cars nway noprint;
class origin drivetrain;
var msrp;
output out=summary n=Count median=MSRP_Med q1=MSRP_Q1 Q3=MSRP_Q3;
run;
data want;
set summary;
count=round(count,5);
array msrp msrp_: ;
do over msrp;
msrp = round(msrp,ifn(msrp>2E4,100,50));
end;
run;
PROC REPORT DATA = want ;
COLUMN Origin DriveTrain Count ('MSRP' MSRP_Med MSRP_Q1 MSRP_Q3);
DEFINE Origin / group;
DEFINE DriveTrain / group;
DEFINE MSRP_Med / analysis median 'Median';
DEFINE MSRP_Q1 / analysis Q1 'Q1';
DEFINE MSRP_Q3 / analysis Q3 'Q3';
RUN;
Results:
Comparison of WORK.SUMMARY with WORK.WANT (Method=EXACT) Values Comparison Summary Number of Variables Compared with All Observations Equal: 0. Number of Variables Compared with Some Observations Unequal: 4. Total Number of Values which Compare Unequal: 31. Maximum Difference: 47.5. All Variables Compared have Unequal Values Variable Type Len Ndif MaxDif Count NUM 8 5 2.000 MSRP_Med NUM 8 9 47.500 MSRP_Q1 NUM 8 8 45.000 MSRP_Q3 NUM 8 9 45.000
Even if PROC REPORT could do that it is probably going to be a lot better to just do the calculations yourself.
proc means data=sashelp.cars nway noprint;
class origin drivetrain;
var msrp;
output out=summary n=Count median=MSRP_Med q1=MSRP_Q1 Q3=MSRP_Q3;
run;
data want;
set summary;
count=round(count,5);
array msrp msrp_: ;
do over msrp;
msrp = round(msrp,ifn(msrp>2E4,100,50));
end;
run;
PROC REPORT DATA = want ;
COLUMN Origin DriveTrain Count ('MSRP' MSRP_Med MSRP_Q1 MSRP_Q3);
DEFINE Origin / group;
DEFINE DriveTrain / group;
DEFINE MSRP_Med / analysis median 'Median';
DEFINE MSRP_Q1 / analysis Q1 'Q1';
DEFINE MSRP_Q3 / analysis Q3 'Q3';
RUN;
Results:
Comparison of WORK.SUMMARY with WORK.WANT (Method=EXACT) Values Comparison Summary Number of Variables Compared with All Observations Equal: 0. Number of Variables Compared with Some Observations Unequal: 4. Total Number of Values which Compare Unequal: 31. Maximum Difference: 47.5. All Variables Compared have Unequal Values Variable Type Len Ndif MaxDif Count NUM 8 5 2.000 MSRP_Med NUM 8 9 47.500 MSRP_Q1 NUM 8 8 45.000 MSRP_Q3 NUM 8 9 45.000
Thanks Tom.
On a related note, now I've added a weight var (Wgt), a random number with mean 5 and SD of 0.1. The Proc Means is applying the weight correctly to the MSRP values, but not to the Count ?
data cars_rounded; set sashelp.cars; Wgt = 5 + 0.1*rannor(3652993); run; proc means data=cars_rounded nway; class origin drivetrain; weight Wgt; var msrp; output out=summary_cars n=Count median=MSRP_Med q1=MSRP_Q1 Q3=MSRP_Q3; run; data want; set summary_cars; count=round(count,5); array msrp msrp_: ; do over msrp; msrp = round(msrp,ifn(msrp>2E4,100,50)); end; run;
I think by default Proc Means doesn't apply weights to n ? But I would like it to do so. The Counts should be like this (multiplied by approximately 5, and then rounded to a multiple of 5):
Count |
170 |
495 |
125 |
180 |
185 |
250 |
110 |
450 |
175 |
Use the SUMWGT= statistic in the OUTPUT statement.
proc means data=cars_rounded nway;
class origin drivetrain;
weight Wgt;
var msrp;
output out=summary_cars n=Count median=MSRP_Med q1=MSRP_Q1 Q3=MSRP_Q3 sumwgt=count_weighted;
run;
Thank you Paige!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.