topic Re: Grouping levels per variable in SAS Programming

Grouping levels per variable

mh2t — Fri, 26 Jun 2020 22:53:05 GMT

I have a dataset :

varName Levels Freq Value

var1 2 11

var1 b 1 12.5

var1 c 1 9.25

var1 d 1 12.5

var2 1 9.25

var2 n 3 8.75

var2 o 1 8

var2 p 1 9.35

var2 q 3 8.15

var2 r 1 5

and I want to bin the levels per variable based on their %0-25, %25-50, %50-75 and %75-100 percentiles of the values within the groups. So the data would like :

varName Levels Freq Value new_level

var1 2 11 2nd

var1 b 1 12.5 3rd

var1 c 1 9.25 1st

var1 d 1 12.5 3rd

var2 1 9.25 3rd

var2 n 3 8.75 4th

var2 o 1 8 2nd

var2 p 1 9.35 4th

var2 q 3 8.15 2nd

var2 r 1 5 1st

Your help would be greatly appreciated!

Re: Grouping levels per variable

ballardw — Fri, 26 Jun 2020 23:06:40 GMT

Please tell us in terms of the variables that you show what defines a "group".

And I think you need to show what the actual 'percentile' value you are assigning to each of those values as I can't see any obvious fit. Are you expecting the freq to affect the percentile value?

Re: Grouping levels per variable

ghosh — Sat, 27 Jun 2020 01:51:27 GMT

Something like this?

proc univariate;
	by varname;
	weight freq;
	var value;
	output pctlpre=P_ 
              pctlpts=0 to 100 by 25;
run;

proc print;
run;

Re: Grouping levels per variable

mh2t — Sat, 27 Jun 2020 02:25:50 GMT

@ballardw group is varName here. Value is representing the average per level. Freq does not affect the percentile value. Percentile relates to the rank order of the values.

Re: Grouping levels per variable

Ksharp — Sat, 27 Jun 2020 11:30:21 GMT

proc rank data=have groups=4 out=want;
by varName;
var value;
ranks new_level;
run;