topic Re: calculate 75 percentile of a variable using proc sql in SAS Programming

calculate 75 percentile of a variable using proc sql

zihdonv19 — Fri, 03 May 2024 12:42:01 GMT

I tried ("cou" is 1,2,3,4,5.....; "var" is continuous variable with decimal points):

proc sql;
    create table mt as
    select cou,
           pctl(75, var) as var_p75
    from check_1
    group by cou;
quit;

But it returns me with multiple rows per cou.

Re: calculate 75 percentile of a variable using proc sql

yabwon — Fri, 03 May 2024 13:15:22 GMT

SQL is not the best tool for this job.

I'd use Proc Univariate, here is nice example: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/procstat/procstat_univariate_examples08.htm

Bart

Re: calculate 75 percentile of a variable using proc sql

PaigeMiller — Fri, 03 May 2024 14:58:58 GMT

While SQL might or might not be able to do this, it is a lot more programming than PROC UNIVARIATE/PROC MEANS/PROC SUMMARY and of course SAS has already tested and debugged these PROCs so you can be sure the answer is correct. If you program it yourself in SQL, I think you have to test, debug and vouch for the accuracy of what you are doing.

Re: calculate 75 percentile of a variable using proc sql

FreelanceReinh — Fri, 03 May 2024 15:32:19 GMT

Just to add the explanation why your approach doesn't work and you should use a different procedure, as others have already suggested:

The PCTL function (unlike the MEDIAN function) is not among the PROC SQL summary functions. Hence, it is not applied to the variable (column) var, but to the single value of var in each observation (row). But for any non-missing numeric value the trivial equality pctl(75, var)=var holds. So, your output dataset MT contains variable cou and basically a copy of variable var, named var_p75, from the input dataset CHECK_1. In particular, it is not aggregated at all, as you have noticed.

You must have received a warning in the log about the absence of a summary function:

WARNING: A GROUP BY clause has been transformed into an ORDER BY clause because neither the SELECT clause nor the optional HAVING clause of the associated
         table-expression referenced a summary function.