CODEWITHSUNDEEP
sqlsas
M Darblade
M Darblade

Reputation: 343

Get quantile by groups in SAS

I have this sort of table :

Cluster    Age    FR
      8     70   153
...

What I want is to get a table : for each Cluster and for each Age, the mean of FR in each 10th quantile. It should look like :

Cluster Age Quantile    FR
1       1   10%         12
1       1   20%         14
1       1   30%         16
1       1   40%         18
1       1   50%         20
1       1   60%         22
1       1   70%         24
1       1   80%         26
1       1   90%         28
1       1   100%        30
1       2   10%         13
1       2   20%         15
1       2   30%         17

I tried doing this with proc univariate but with no success...

proc univariate data=etude.Presta_cluster_panier noprint;
    var FR;
    output out=pctls pctlpre=P_ pctlpts=0 to 100 by 10;
run;

Upvotes: 3

Views: 3348

Answers (2)

JJFord3
JJFord3

Reputation: 1985

This can be accomplished in two step through the use of proc rank & proc means.

proc rank data=etude.Presta_cluster_panier out=outranks groups=10;
    var FR;
    ranks Quantile;
    by Cluster Age;
run;

proc means data=outranks;
    var FR;
    ways 3;
    class Cluster Age Quantile;
    output out=outmean;
run;

Upvotes: 2

M4hd1
M4hd1

Reputation: 133

You will need to first obtain your quartiles by cluster and age. Then remerge with your master dataset, assign groups depending on your quartiles and finally compute the mean buy cluster age and quartile.

It is not possible in one step.

Upvotes: 1

Related Questions