calculate 75 percentile of a variable using proc sql

[zihdonv19]

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.

[yabwon · Accepted solution]

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

[PaigeMiller]

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.

[FreelanceReinh]

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.