Adding cost of services grouped by month AND client

[LittleTurtle]

Hi, 

I am working on a project where I would need to add the cost of services that is grouped by the month and client. Below is an example of how I would like the table to be:

Client_Name | Cost_Of_Services | Service Month | Jul_Cost | Aug_Cost |

    Bobby        |       $15.00             |          1             |   $54.00  |     $26.00  |

    Bobby        |       $15.00             |          1             |   $54.00  |     $26.00  |

    Bobby        |       $24.00             |          1             |   $54.00  |     $26.00  |

    Bobby        |       $10.00             |          2             |   $54.00  |     $26.00  |

    Bobby        |       $11.00             |          2             |   $54.00  |     $26.00  |

    Bobby        |       $5.00               |          2             |   $54.00  |     $26.00  |

    James        |       $20.00             |          1             |   $41.00  |     $0         |

    James        |       $21.00             |          1             |   $41.00  |     $0         |

So far, the steps that I have tried was grouping the data by Client_Name and then by Service month. Then create a computed column to add DISTINCT Cost_Of_Services. This works but if there is a duplicate in the cost of service for a service month, such as the above table has, it does not add them correctly.

If anyone can help on this that would be great. Thank you!

[PeterClemmensen]

Welcome to the SAS communities 🙂

Can you show us what you want the desired output to look like? Makes it a lot easier to help you

[LittleTurtle]

The table I have drawn out was how I would like it to be. Service Month 1 = July and Service Month 2 = August. At first it will look for all rows with Client_Name 'Bobby' and Service month '1'. All rows that match this will then add up which gives a total of $54. Then the next sequence will look for all rows with Client_Name 'Bobby' but Service Month '2' which will add up to 26$.

Then the next sequence will look for James and start at Service Month '1' and add up the costs of service which is $41.

[r_behata]

Not the Optimal way of doing this but see if this works for you.

data have;
input Client_name $ Cost_of_service service_month;
cards;
bobby 15 1
bobby 15 1
bobby 24 1
bobby 10 2
bobby 11 2
bobby 5 2
james 20 1
james 21 1
;
run;

Proc summary data=have nway;
	var Cost_of_service;
	class Client_name service_month;
	output out= temp(drop=_freq_ _type_) sum= ;
run;

proc transpose data=temp out=temp_out(drop=_name_ rename=("1"n=Jul_Cost_ "2"n=Aug_Cost_));
	var Cost_of_service;
	id service_month;
	by Client_name ;
run;

data want;
	merge have temp_out;
	 by Client_name;
run;