BookmarkSubscribeRSS Feed
LittleTurtle
Calcite | Level 5

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!

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

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
Calcite | Level 5

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
Barite | Level 11

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;

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 775 views
  • 0 likes
  • 3 in conversation