QUARTILES AGGREGATION

[Andres_Fuentes1]

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.

[Reeza]

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.

---

[ballardw · Accepted solution]

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

[Andres_Fuentes1]

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

[ballardw]

---

@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.