Aggregation of Data

[RebeccaB_]

Hello,

I am trying to aggregate a dataset. Each row of the dataset provides information on a drug seizure. I would like to combine all similar date of seizure, drug name, and county where it occurred and then use the sum function to add the quantity. There are many dates so I was wondering if could use prxmatch to search for all similar dates, drug, and county and then sum the quantity. I'm having difficulty finding an example of how to do this. Thank you in advance for any advice. 

Ex. 

Date_of_Seizure    Drug_Name  Quantity     Unit      County 

MM/DD/YYYY         Char               Numeric      Char     Char

[Reeza]

Dates are numeric so you can combine them based on distance. How are you defining 'similar dates'?

[RebeccaB_]

I need to match the exact month, day, and year for the seizure date (similar dates) to the drug and then the county where it was seized.

For example,
Seizure date Drug Quantity Unit County
12/31/2000 Marijuana 2.1 kg Something
12/31/2000 Marijuana 0.4 kg Something
12/31/2000 Marijuana 1.75 kg Something

What I would like to have:
12/31/2000 Marijuana 4.25 kg Something

[Tom · Accepted solution]

Sounds like you just want to use the DATE, DRUG and COUNTY to GROUP the data.

Since you have UNIT variable you should also group by that one.  You cannot really sum kilograms and pounds and ounces and grams without first converting the numbers to equivalent units.

data have;
  input Seizure_date :mmddyy10. Drug :$20. Quantity Unit $ County :$20. ;
  format Seizure_date yymmdd10.;
cards;
12/31/2000 Marijuana 2.1 kg Something
12/31/2000 Marijuana 0.4 kg Something
12/31/2000 Marijuana 1.75 kg Something
;

proc summary data=have nway ;
  class Seizure_date Drug Unit County;
  var Quantity;
  output out=want n=N_seizures sum=Total_Quantity ;
run;

Result

         Seizure_                                                                         Total_
Obs          date      Drug       Unit     County      _TYPE_    _FREQ_    N_seizures    Quantity

 1     2000-12-31    Marijuana     kg     Something      15         3           3          4.25

PS Avoid displaying dates in MDY or DMY order.  Either one you pick will confuse %50 of your audience.