topic Conditional Loops / Aggregation of Data in SAS Programming

Conditional Loops / Aggregation of Data

ali11 — Tue, 14 Jan 2020 00:18:29 GMT

Hello! I'm trying to extract sum information from a relatively big dataset I'm working with. At this point, I have information about thousands of companies identified by CUSIP (a unique identifier), prices and quantity data and corresponding dates. Here is what I want to do:

I am trying to write a loop (perfect if a loop is not necessary indeed) that selects a specific company (by CUSIP) and produce a value weighted number (Price*Quantity)/(Sum(Quantity| for a specific date) such that for example according to the image below, row 1-7 is replaced by a single row containing the aggregated data (CUSIP and date remain the same). I do not know where to start and any hints would be very much appreciated. FYI, I studied how loops work in SAS, but I do not know how to write a loop that at each step selects a CUSIP for a specific Execution_date and performs the aggregation, saves it and moves to the second CUSIP-date combination.

Thanks in advance!

Re: Conditional Loops / Aggregation of Data

PaigeMiller — Tue, 14 Jan 2020 01:45:47 GMT

What you are describing is a weighted avereage. This is done via PROC SUMMARY, no looping neeed.

Something like this:

proc summary data=have nway;
     class cusip_id execution_date;
     var price/weight=quantity;
     output out=aggregation mean=;
run;

Suggestion: since you are using SAS, which is one of the leading statistical analysis available, if not the leading statistical package available, please know that an extremely large number of analyses have been built into SAS, including summing and computing means. Before you come to the conclusion that you will have to program some loops somehow to do this, please save yourself some time and ask/investigate if it has already been programmed.

Re: Conditional Loops / Aggregation of Data

ali11 — Tue, 14 Jan 2020 16:00:22 GMT

thanks. very helpful and just what I needed.