topic Re: PCLT of data in colum using SAS guide in SAS Programming

PCLT of data in colum using SAS guide

AnaC — Mon, 04 Feb 2019 15:36:43 GMT

Hello

I'm trying to calculate the 98th percentil of a data in a colum by two variables. I'm doing this in SAS guide, so building a small programm could be the solution. This is what 've donne base in sas code:

PROC SORT
DATA=WORK.TABLE_1 (KEEP=Cost Class)
OUT=WORK.SORTTempTableSorted
;
BY Class;
RUN;

PROC MEANS DATA=WORK.SORTTempTableSorted

P99 ; (here I would like to be able to chose pe 87%)
VAR Cust;
BY Class;

RUN;

But this is not in I table were a can resuse the values.

I also tried to do directly in a query with a calculaed field PCTL(87%, Cost) but the result is a table with one colum and as may lines as the original tabel.

Does someone knows how to get this values?

Thanks,

Ana

Re: PCLT of data in colum using SAS guide

ballardw — Mon, 04 Feb 2019 15:52:47 GMT

You may want to look at Proc Univariate and the PCTLPTS option to get different percentile points than Proc Means supports.

Or show the code you attempted that did "I also tried to do directly in a query with a calculaed field PCTL(87%, Cost) but the result is a table with one colum and as may lines as the original table."

Which sounds like you may have tried something with Proc SQL, missing a Group clause and not including your Cust variable on the select.

Re: PCLT of data in colum using SAS guide

RW9 — Mon, 04 Feb 2019 16:02:20 GMT

Avoid coding all in uppercase, and there is a code window above the post area - {i}. With this you can get output like:

proc means data=sorttemptablesorted;
  var cust;
  by class;
  output out=want  p99=p99;  /* Specify list of calculations here */
run;

So you get an output dataset by using the output out=, or you can also select specific sections of outputs first by running:

ods trace on;

...yourcode...

ods trace off;

This will show in the log or output what elements are created by the procedure, you can then select these like (say I want means output to be saved to want):

ods output means=want;

...your code...

Re: PCLT of data in colum using SAS guide

BrunoMueller — Mon, 04 Feb 2019 16:22:21 GMT

also have a look at this blog https://blogs.sas.com/content/iml/2013/10/23/percentiles-in-a-tabular-format.html by @Rick_SAS

Re: PCLT of data in colum using SAS guide

AnaC — Mon, 04 Feb 2019 16:46:07 GMT

Thank you so much. Combining all your anserws I come up with this code that work perfectly with what I need:

PROC SORT
DATA=WORK.SIN_C_DIAS_ITA_ITP(KEEP=Cost Class Type)
OUT=WORK.SORTTempTableSorted
;
BY Class Type;
RUN;

PROC univariate DATA=WORK.SORTTempTableSorted noprint;

VAR Costo;
BY Class Type;

output out=Percentil pctlpre=Class_ pctlpts=98;

RUN;

PROC SQL;

drop table SORTTempTableSorted;

Quit;

Re: PCLT of data in colum using SAS guide

Reeza — Tue, 05 Feb 2019 20:28:31 GMT

You can simplify this a bit, especially if your data is small. Is your variable cost or costo? You seem to refer to both?

PROC univariate  DATA=WORK.SIN_C_DIAS_ITA_ITP(KEEP=Cost Class Type) NWAY noprint;
CLASS Class Type;
Var Cost;
 output out=Percentil pctlpre=Class_ pctlpts=98;
run; quit;

To avoid sorting you can use a CLASS statement instead of BY, and add the NWAY option to get only the highest levels of combinations. Remove it to see what you get instead if interested.

Changes are in purple.

@AnaC wrote:

Thank you so much. Combining all your anserws I come up with this code that work perfectly with what I need:

PROC SORT
DATA=WORK.SIN_C_DIAS_ITA_ITP(KEEP=Cost Class Type)
OUT=WORK.SORTTempTableSorted
;
BY Class Type;
RUN;

PROC univariate DATA=WORK.SORTTempTableSorted noprint;

VAR Costo;
BY Class Type;

output out=Percentil pctlpre=Class_ pctlpts=98;

RUN;

PROC SQL;

drop table SORTTempTableSorted;

Quit;