topic Summing up data by quarters in New SAS User

Summing up data by quarters

maliha9999 — Mon, 17 Jul 2023 14:19:00 GMT

Hi everyone,

I have the following dataset. Where I have created a new variable called quarter. I would like to sum together my Sales_Total for each City_Village by quarter. Then drop the month, year and date variables. My original dataset has sales values by month.

City_ID	Reporting_YEAR	Reporting_MONTH	City_Village	Sales_Total	date	quarter
1	2018	10	A	155	21458	2018Q4
1	2018	11	A	134	21489	2018Q4
1	2018	12	A	141	21519	2018Q4
1	2019	1	A	153	21550	2019Q1
1	2019	2	A	134	21581	2019Q1
1	2019	3	A	145	21609	2019Q1
2	2018	10	B	155	21458	2018Q4
2	2018	11	B	134	21489	2018Q4
2	2018	12	B	141	21519	2018Q4
2	2019	1	B	153	21550	2019Q1
2	2019	2	B	134	21581	2019Q1
2	2019	3	B	147	21609	2019Q1

Below is the dataset I would like based on the sample dataset above. (my actual dataset has a lot more towns)

City_ID	City_Village	Sales_Total	quarter
1	A	430	2018Q4
1	A	432	2019Q1
2	B	430	2018Q4
2	B	434	2019Q1

Any suggestions for how to start the code to get the final dataset would be greatly appreciated.

Thank you,

Maliha

Re: Summing up data by quarters

PaigeMiller — Mon, 17 Jul 2023 14:27:02 GMT

This is very simple using PROC SUMMARY.

proc summary data=have nway;
    class city_village quarter;
    var sales_total;
    output out=want sum=;
run;

In fact, you don't need to create the variable QUARTER.

proc summary data=have nway;
    class city_village date;
    var sales_total;
    format date yyq7.;
    output out=want sum=;
run;

Re: Summing up data by quarters

ballardw — Mon, 17 Jul 2023 14:25:50 GMT

When you have actual SAS date values often you do not need to create variables such as your Quarter as just applying the correct format in a reporting or analysis procedure will group the records as needed.

Since you didn't actually provide code to make a data set this is my take:

Proc summary data=have nway;
   class city_Id city_village date;
   format date yyQ6. ;
   var sales_total;
   output out=want (drop= _type_ _freq_) sum=;
run;