Group and sum observations

[aperansi]

The main objective I need to accomplish is grouping all observations by the LAP column while also gettinga SUM of all the prices associated with a each LAP. 

Some detail about the report that I think may help are as follows:

• The api_call column is a representation of a specific product that we order.
• Each api_call on the dataset has a different price associated with it depending on the product. 
  • Not all products are showing on the below screenshot
• The LAP column encompass multiple api_calls keys. 

Below is a picture of the dataset I am currently working with. 

Please let me know if there is any information I need to provide in order to make this easier to solve. Thank you for your time!

[PaigeMiller]

---

@aperansi wrote:

The main objective I need to accomplish is grouping all observations by the LAP column while also gettinga SUM of all the prices associated with a each LAP. 

Some detail about the report that I think may help are as follows:

• The api_call column is a representation of a specific product that we order.
• Each api_call on the dataset has a different price associated with it depending on the product. 
  • Not all products are showing on the below screenshot
• The LAP column encompass multiple api_calls keys. 

---

Well, from the first sentence, you seem to want a result that ignores api_call, but you spend some time discussing api_call, which seems to indicate it is of some importance. Perhaps you can clarify?

Anyway, here is some code that might get what you want:

proc summary data=have;
    class lap api_call;
    types lap lap*api_call;
    var price;
    output out=want sum=;
run;

[aperansi]

Hey Paige, Thank you for your reply. The reason the api_call column is important is that each row that has an api_call has a unique price associated with it. The LAP column is essentially a group of api_calls. In order to get the price of one LAP, I need the various api_calls added together to get me the price. Hope this explanation helps.

I tried to code you wrote out and that didnt work for a few reason. I need the date added in there so i can get the total price for each month down the line.

Also: when I scroll through the data, I am still seeing duplicate LAP keys. There should only be one of these. The only reasons there would be two is because there are multiple api_calls per one LAP.

[PaigeMiller]

---

@aperansi wrote:
Hey Paige, Thank you for your reply. The reason the api_call column is important is that each row that has an api_call has a unique price associated with it. The LAP column is essentially a group of api_calls. In order to get the price of one LAP, I need the various api_calls added together to get me the price. Hope this explanation helps.

---

This translates in my mind to: you need the sum of price within each LAP. Is that right? I guess I don't see the importance of api_call, you can get the sums without ever looking at the actual api_call number. Is that right?

I need the date added in there so i can get the total price for each month down the line.

Is the date column an actual SAS date value, formatted to 2019-04, or is it truly the text string 2019-04?

[aperansi]

I think we are on the same page. I want the total price of each LAP. So if a LAP appears on there more than once, all the LAPS should be summed and the outcome should only be one LAP.

The date is a SAS date value I believe. It is currently formatted as yymmdd10. I originally got the date from a sql query and converted the datetime using datepart function.

[PaigeMiller · Accepted solution]

proc summary data=have nway;
    class date lap;
    var price;
    output out=want sum=;
run;

[aperansi]

Thank you for your help Paige!