topic Re: Rounding to nearest multiple with Proc Report in SAS Programming

Rounding to nearest multiple with Proc Report

DougHold — Mon, 25 Apr 2022 02:07:24 GMT

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:

Count rounded to multiple of 5
MSRP rounded to multiple of 100 if the value is $20,000 or greater. Otherwise, rounded to a multiple of 50 if the value is less than $20,000.

			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,

Re: Rounding to nearest multiple with Proc Report

Tom — Mon, 25 Apr 2022 04:58:19 GMT

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

Re: Rounding to nearest multiple with Proc Report

DougHold — Mon, 25 Apr 2022 16:45:07 GMT

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

Re: Rounding to nearest multiple with Proc Report

PaigeMiller — Tue, 26 Apr 2022 12:09:31 GMT

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;

Re: Rounding to nearest multiple with Proc Report

DougHold — Wed, 27 Apr 2022 02:09:07 GMT

Thank you Paige!