topic QUARTILES AGGREGATION in SAS Programming

QUARTILES AGGREGATION

Andres_Fuentes1 — Mon, 14 Feb 2022 19:48:32 GMT

Dear, I hope you are very well, I have had enough problems in SAS GUIDE to generate an aggregation according to quartiles.

i have

DATE	VALUES
01-01-2022	25
01-01-2022	35
01-01-2022	42
01-01-2022	12
01-01-2022	33
01-01-2022	6
02-01-2022	19
02-01-2022	63
02-01-2022	54
02-01-2022	12
02-01-2022	58
02-01-2022	100

and i need the result like this:

DATE	Q1	Q2	Q3
01-01-2022	10,5	29	36,75
02-01-2022	17,25	56	72,25

Thanks you so much.

Re: QUARTILES AGGREGATION

Reeza — Mon, 14 Feb 2022 20:25:08 GMT

Are you using tasks or programming?

If tasks, this is likely under a summarize task, and you can select Q1, Median, Q5 and group by your date.

If code, this will place the results in a data set called WANT as well as display them.

proc means data=have  NWAY Q1 Median Q3;
class date;
var values;
ods output summary = want;
run;

@Andres_Fuentes1 wrote:

Dear, I hope you are very well, I have had enough problems in SAS GUIDE to generate an aggregation according to quartiles.

i have

DATE VALUES

01-01-2022 25

01-01-2022 35

01-01-2022 42

01-01-2022 12

01-01-2022 33

01-01-2022 6

02-01-2022 19

02-01-2022 63

02-01-2022 54

02-01-2022 12

02-01-2022 58

02-01-2022 100

and i need the result like this:

DATE Q1 Q2 Q3

01-01-2022 10,5 29 36,75

02-01-2022 17,25 56 72,25

Thanks you so much.

Re: QUARTILES AGGREGATION

ballardw — Mon, 14 Feb 2022 20:29:46 GMT

Is that Q1 supposed to be the first quartile for the given data? If so you need to provide which definition of quartile you are using. SAS can use five different quantile definitions. It appears you may want to use the QNTLDEF=4 in your calculations:

data have;
   input DATE :mmddyy10.	VALUES ;
   format date mmddyy10.;
datalines;
01-01-2022	25
01-01-2022	35
01-01-2022	42
01-01-2022	12
01-01-2022	33
01-01-2022	6
02-01-2022	19
02-01-2022	63
02-01-2022	54
02-01-2022	12
02-01-2022	58
02-01-2022	100
run;

proc means data=have q1 median q3 qntldef=4;
   class date;
   var values;
run;

Which yields:

Analysis Variable : VALUES
DATE	N Obs	Lower Quartile	Median	Upper Quartile
01/01/22	6	10.5000000	29.0000000	36.7500000
02/01/22	6	17.2500000	56.0000000	72.2500000

Re: QUARTILES AGGREGATION

Andres_Fuentes1 — Tue, 15 Feb 2022 12:58:48 GMT

thanks for the solution, another question for the same code.
how can i change the order in the table out descending by date?
thanks

Re: QUARTILES AGGREGATION

ballardw — Tue, 15 Feb 2022 16:02:26 GMT

@Andres_Fuentes1 wrote:
thanks for the solution, another question for the same code.
how can i change the order in the table out descending by date?
thanks

Multiple ways.

With Proc Means/summary create an output data set and then sort. There will be some other variables by default and need another option to control the levels of the output

proc means data=have nway qntldef=4;
   class date;
   var values;
   output out=work.quantiles q1= median= q3= /autoname ;
run;

Proc sort data=work.quantiles;
   by descending date;
run;

Other options would be Proc Report or Tabulate which will calculate the same statistics but provide different tools for ordering output in the created table.