topic Group and sum observations in New SAS User

Group and sum observations

aperansi — Thu, 25 Apr 2019 19:51:08 GMT

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!

Re: Group and sum observations

PaigeMiller — Thu, 25 Apr 2019 20:03:07 GMT

@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;

Re: Group and sum observations

aperansi — Fri, 26 Apr 2019 13:31:35 GMT

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.

Re: Group and sum observations

PaigeMiller — Fri, 26 Apr 2019 14:13:50 GMT

@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?

Re: Group and sum observations

aperansi — Fri, 26 Apr 2019 14:18:10 GMT

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.

Re: Group and sum observations

PaigeMiller — Fri, 26 Apr 2019 14:22:21 GMT

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

Re: Group and sum observations

aperansi — Fri, 26 Apr 2019 18:27:57 GMT

Thank you for your help Paige!