topic Re: summing one column when variables are duplicates in other columns in SAS Programming

summing one column when variables are duplicates in other columns

maliha9999 — Mon, 08 May 2023 07:09:54 GMT

Hello,

I am new to using SAS and I having trouble with some data cleanup. Below is my dataset. I have cleaned up my original dataset to create a dataset that contains all duplicates for the reporting month, year and city_village.

Obs Reporting_YEAR Reporting_MONTH City_Village Sales_Total date123456

2019	6	A	$5000	01JUN2019
2019	6	A	$10	01JUN2019
2019	7	B	$90	01JUL2019
2019	7	B	$100	01JUL2019
2019	8	C	$5	01AUG2019
2019	8	C	$6	01AUG2019

What I would like to do is merge together the sum of Sales_Total in one entry when the date and city_village are the same. For this example dataset I would like the following result.

Obs Reporting_YEAR Reporting_MONTH City_Village Sales_Total date135

2019	6	A	$5010	01JUN2019
2019	7	B	$190	01JUL2019
2019	8	C	$11	01AUG2019

This is the code I have tried so far, instead of adding the sales total for duplicate year, month and city, it adds up the duplicate dates instead. For instance, some entries have different city_town for the date, so the code ends up merging according to the date as opposed to the same date and city_town. I am not sure how to include an additional variable in the by-statement below.

proc summary data = all; 

by date;

var Sales_Total;

output out =want sum() = ;

run;

Thank you, any help would be appreciated.

Re: summing one column when variables are duplicates in other columns

PeterClemmensen — Mon, 08 May 2023 07:21:10 GMT

Try this

data have;
input Reporting_YEAR Reporting_MONTH City_Village $ Sales_Total :dollar6. date123456 :date9.;
format date123456 date9.;
datalines;
2019 6 A $5000 01JUN2019
2019 6 A $10 01JUN2019
2019 7 B $90 01JUL2019
2019 7 B $100 01JUL2019
2019 8 C $5 01AUG2019
2019 8 C $6 01AUG2019
;

proc summary data = have nway;
   class Reporting_YEAR Reporting_MONTH City_Village date123456 ;
   var Sales_Total;
   output out = want(drop = _:) sum=;
run;

Re: summing one column when variables are duplicates in other columns

maliha9999 — Mon, 08 May 2023 16:15:05 GMT

Thank you PeterClemmensen. The code worked and got me the dataset I wanted. 🙂