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.
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 |
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.
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 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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.