topic Adding cost of services grouped by month AND client in New SAS User

Adding cost of services grouped by month AND client

LittleTurtle — Tue, 04 Dec 2018 20:21:47 GMT

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:

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!

Re: Adding cost of services grouped by month AND client

PeterClemmensen — Tue, 04 Dec 2018 21:12:30 GMT

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

Re: Adding cost of services grouped by month AND client

LittleTurtle — Tue, 04 Dec 2018 22:13:23 GMT

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.

Re: Adding cost of services grouped by month AND client

r_behata — Tue, 04 Dec 2018 23:16:37 GMT

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;