topic Re: Sorting dataset by percentile in SAS Programming

Sorting dataset by percentile

igsteo — Thu, 28 Jan 2021 07:33:04 GMT

I have to sort a dataset by 5 sizes i.e. size 1 (bottom 20%), size 2 (20-40%), size 3 (40-60%), size 4 (60-80%) and size 5 (top 20%).

I require some feedback as to whether proc univariate or proc rank would be more approriate?

thank you

proc univariate data=iglib.mktcap6yrs noprint; 
by dsname;
var CY; 
output out=mktcapsorted pctlpre=P pctlpts=10 to 100 by 20; 
run;

proc rank  data= mydataset groups=5
    out=rankedset;
   by age;
   var var2;
   ranks varrank;
   ;
run;

Re: Sorting dataset by percentile

Astounding — Thu, 28 Jan 2021 09:52:52 GMT

To sort the data use PROC SORT. If all you need is data in order by percentile then your work is done. The program doesn't assign percentile but it does put the data in the right order. If you need percentile assignments added to the data, you are not ready yet. You first need to decide how to handle ties. If half the data has a value of zero for the analysis variable, you have to choose. Do you want one percentile that is much larger than the others? Do you want to split the values so that the same value gets split across two (or more) percentiles? PROC RANK has options to easily apply any decision you might make along these lines.

Re: Sorting dataset by percentile

igsteo — Fri, 29 Jan 2021 03:59:45 GMT

yup i just need to rank them from size 1 to 5 which i have managed to achieve through the proc rank group=5.

Is there a way to rename each rank? i.e. 0 = small...5=big.

Re: Sorting dataset by percentile

Astounding — Fri, 29 Jan 2021 20:46:25 GMT

You wouldn't change the data. But you could change how the values print by using PROC FORMAT, then apply the format when printing to translate the five numbers into words.