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