topic Re: best way to sum values in a variable with a large dataset in SAS Procedures

best way to sum values in a variable with a large dataset

pinkyc — Fri, 30 Jan 2015 19:25:24 GMT

I have input data like this:

Pat_ID Discharge_Date Paid

AA 55.00

AA 0.00

AA 01/01/2012 -180.00

AA 04/01/2012 44.00

AA 37.00

AA 54.00

AA 0.00

BB -200

BB 09/23/2012 0.00

BB 54.00

I need to sum Paid by two ways, just grouping by Pat_ID and alternately by Discharge_Date and Pat_ID (so only the ones with not blank discharge dates are summed). I know I can just create a sub-table with If Discharge_Date NE '' and sum the remaining Pat_ID's but was wondering if there's a way to do it in one step, more or less? I also have 1580312 observations so I wonder if a proc sql method is better than the data step and sum.

Re: best way to sum values in a variable with a large dataset

Reeza — Fri, 30 Jan 2015 19:39:39 GMT

I would recommend proc means/univariate/summary, especially since you can calculate sub-totals without an additional step.

Look into the CLASS, WAYS and TYPE statement in proc means.

Re: best way to sum values in a variable with a large dataset

ballardw — Fri, 30 Jan 2015 20:37:02 GMT

I agree with Reeza. With an output data set from proc summary there will be a variable _type_ that describes the combinations of the class variables.

Proc summary data=have;

class Pat_id Discharge_date;

var paid;

output out=want sum=;

run;

for example.

By default you well get a sum for 1) all records 2) each Pat_id 3) each Discharge_date and 4) each unique combination of Pat_id and Discharge_date.