topic Re: percentiles by group that is defined by 2 fields in SAS Programming

percentiles by group that is defined by 2 fields

Ronein — Mon, 06 Jun 2022 06:41:28 GMT

Hello

I want to calculate percentiles (P50,P70,P80,P90) for each group.

The group is defined by the fields old and gk.

There is a problem in the result of the following code:

I get duplication of groups.

For example: For gk='(a) 2-3' I get 2 rows with old='(a) 0-10'

What is the way to solve it please?

Why did it happen?


data have;
input gk old New;
cards;
2 10 15
2 15 22
2 20 25
2 17 19
2 19 17
2 31 35
2 35 40
2 27 30
2 18 18
2 17 19
3 72 72
3 12 10
3 19 20
3 10 15
3 9 10
3 42 45
3 35 30
5 37 40
5 27 30
5 29 29
5 32 33
5 35 36
5 65 70
5 9 10
5 13 12
5 18 15
;
run;

proc format;
value gkfmt
2,3='(a) 2-3'
5='(b) 5'
;
Run;

proc format;
value old_fmt
0-10='(a) 0-10'
10-20='(b) 10-20'
20-high='(d) 20+'
;
Run;

proc sort data=have;
by gk old;
Run;

proc summary data=have;
by gk old ;
var new: ;
output out=want(drop=_type_) p50= p70= P80=  P90=/autoname;
format  old old_fmt.  gk gkfmt.;
run;

Re: percentiles by group that is defined by 2 fields

Kurt_Bremser — Mon, 06 Jun 2022 08:07:54 GMT

In oldfmt, the values 10 and 20 appear in two ranges each, so the multilabel-capable PROC SUMMARY will honor that. This may cause incorrect calculations.

Regarding the duplicates:

By sorting and using BY instead of CLASS, the combination of 2 and 10 appears before 2 and 15, but much later you get 3 and 10, which falls into the same formatted range as and 10. But then the values for the group have already been calculated once, so you get a second entry.

Fix your format definition, and use CLASS and the NWAY option in the PROC SUMMARY statement.

Re: percentiles by group that is defined by 2 fields

Ronein — Mon, 06 Jun 2022 11:03:50 GMT

Thanks,I write the solution by your advice


data have;
input gk old New;
cards;
2 10 15
2 15 22
2 20 25
2 17 19
2 19 17
2 31 35
2 35 40
2 27 30
2 18 18
2 17 19
3 72 72
3 12 10
3 19 20
3 10 15
3 9 10
3 42 45
3 35 30
5 37 40
5 27 30
5 29 29
5 32 33
5 35 36
5 65 70
5 9 10
5 13 12
5 18 15
;
run;
proc format;
value gkfmt
2,3='(a) 2-3'
5='(b) 5'
;
Run;
proc format;
value old_fmt
0-10='(a) 0-10'
10<-20='(b) 10-20'
20<-high='(d) 20+'
;
Run;

/**Way1**/
proc summary data=have nway;
class gk old ;
var new ;
output out=want(drop=_type_) p50= p70= P80=  P90=/autoname;
format  old old_fmt.  gk gkfmt.;
run;


/**Way2**/
proc sort data=have;
by gk old ;
Run;
proc summary data=have nway;
class gk old ;
var new ;
output out=want(drop=_type_) p50= p70= P80=  P90=/autoname;
format  old old_fmt.  gk gkfmt.;
run;

Re: percentiles by group that is defined by 2 fields

Ronein — Mon, 06 Jun 2022 11:40:03 GMT

As I see, if the value appear in 2 ranges (proc format) then the value will be allocated to first range.
Is it still "multilabel" case??

data ttt;
input x;
cards;
9
9.9
10
15
19
20
25
40
;
run;

proc means data=ttt;
class x;
format x old_fmt.;
Run;