topic Re: Number of observations in each percentile in SAS Programming

Number of observations in each percentile

PSP_1 — Thu, 09 May 2019 22:38:03 GMT

Hi,

I am new to SAS and I struggle to find a way for my problem.

I have a data set having variables:

va1 var2 age

I calculated the percentiles using the code below and this worked well:

proc univatiate data=mydataset;

by age;

weight var1;

var var2;

output out = mynew_data

PctlPre=PERC_

PctPts=0 to 100 by 1;

run;

I now would like to determine how many observations there are within each percentile and I am really struggling. I need to have the output to be

p1 p2 etc...

age1 n obs n obs

age2

etc..

Your help would be much appreciated.

Re: Number of observations in each percentile

ballardw — Thu, 09 May 2019 22:55:34 GMT

I think that you may want Proc Rank for this specific task;

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

Will add a variable VARRANK to the data which indicates which percentile the record belongs to.

Then use proc freq/report/tabulate to count age values by the varrank values.

Re: Number of observations in each percentile

Reeza — Thu, 09 May 2019 23:00:56 GMT

PROC RANK doesn't have a WEIGHT statement so the percentiles wouldn't be calculated correctly 😞

Re: Number of observations in each percentile

Reeza — Fri, 10 May 2019 02:33:00 GMT

Here's one way - please read the comments this will need some tweaking to ensure you get the correct values.

You didn't provide sample data so I worked with the Stocks data available, replace with your data set and variable names and you should be fine.

*add weights to fake data;

data stocks;
    call streaminit(20);
    set sashelp.stocks;
    myWeight=rand('integer', 1, 100);
run;

*get percentiles;

proc univariate data=stocks noprint;
    by stock;
    weight myWeight;
    var open;
    output out=myNewData PctlPre=PERC_ PctlPts=0 to 100 by 1;
run;

*merge into main data and calculate percentile using an array;
*you may want to check the <= its likely not right;
*and the index to see how you want to deal with records above/below;

data calcs;
    merge stocks myNewData;
    by stock;
    array pct(*) perc:;

    do i=2 to dim(pct);

        if pct(i-1) <=open <=pct(i) then
            index=i-1;
    end;
    drop perc:;
run;

*get frequencies;

proc freq data=calcs noprint;
    table stock*index / out=long missing;
run;

*transpose to desired format - if just printing you dont need this
step;

proc transpose data=long out=wide prefix=PRCT_;
    by stock;
    id index;
    var count;
run;

*print for display;

proc print data=wide;
run;

@PSP_1 wrote:

Hi,

I am new to SAS and I struggle to find a way for my problem.

I have a data set having variables:

va1 var2 age

I calculated the percentiles using the code below and this worked well:

proc univatiate data=mydataset;

        by age;

        weight var1;

        var var2;

output out = mynew_data

           PctlPre=PERC_

           PctPts=0 to 100 by 1;

run;

I now would like to determine how many observations there are within each percentile and I am really struggling. I need to have the output to be

           p1         p2       etc...

age1       n obs         n obs

age2

etc..

Your help would be much appreciated.

Re: Number of observations in each percentile

PSP_1 — Fri, 10 May 2019 15:26:12 GMT

Thank you so, so much! The code worked perfectly well and it solved my problem!

Thanks again for your time!

Re: Number of observations in each percentile

Reeza — Fri, 10 May 2019 15:27:49 GMT

It may be helpful to post your solution as well, since you had to tweak it to get the right values.

Re: Number of observations in each percentile

PSP_1 — Tue, 14 May 2019 15:48:07 GMT

Yes, sure.

The only change I made to the code was in the below section:

data calcs;
merge stocks myNewData;
by stock;
array pct(*) perc:;

do i=2 to dim(pct);

if pct(i-1) <=open <=pct(i) then

* on the line below I changed from index = i-1 to index = i-2 otherwise the percentiles were shifted by 1;;

index=i-2;
end;
drop perc:;
run;

Apart of one line, everything else I used as is.