topic Re: Aggregation of Data in SAS Programming

Aggregation of Data

RebeccaB_ — Tue, 18 Oct 2022 15:39:02 GMT

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

Re: Aggregation of Data

Reeza — Tue, 18 Oct 2022 15:42:45 GMT

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

Re: Aggregation of Data

RebeccaB_ — Tue, 18 Oct 2022 15:58:27 GMT

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

Re: Aggregation of Data

Tom — Tue, 18 Oct 2022 16:06:55 GMT

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.