BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LoPez_Diaz
Obsidian | Level 7

Hello , 

I'm new to SAS EG , and i'm trying to perform some data cleaning.
I want to sum a column depending on the value of two other columns. 
Here is a scope of the data I have : 

CODECLIENTAMOUNT
M0013R201300
M0013R201500
M0013R201600
M0013R200900
M0013R200300
M0013R201400
M0013R200200
T0198K200700
T0198K200200
U8910E201600
U8910E200300
P9823D200400
P9823D2001800
P9823D201600
P9823D200600

 

The Code is an alpha-numerical column , 

The CLIENT column can have only 2 values : 200 or 201  .

I want to vertically sum the amount column when the code is the same and the client is the same.

The data I want should look like this : 

CODECLIENTAMOUNT
M0013R2011800
M0013R2001400
T0198K200900
U8910E201600
U8910E200300
P9823D2002800
P9823D201600

 

Thank you in advance for your responses.

 

1 ACCEPTED SOLUTION

Accepted Solutions
heffo
Pyrite | Level 9

As far as I can see it is the same as the OP had in the post.

 

*Creating the dummy data;
data have;
	input CODE $ CLIENT	AMOUNT;
datalines;
M0013R 201 300
M0013R 201 500
M0013R 201 600
M0013R 200 900
M0013R 200 300
M0013R 201 400
M0013R 200 200
T0198K 200 700
T0198K 200 200
U8910E 201 600
U8910E 200 300
P9823D 200 400
P9823D 200 1800
P9823D 201 600
P9823D 200 600
;;;
run;


*This is the code that the Query Builder gives you when following my instructions;
%_eg_conditional_dropds(WORK.QUERY_FOR_HAVE);

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_HAVE AS 
   SELECT DISTINCT t1.CODE, 
          t1.CLIENT, 
          /* SUM_of_AMOUNT */
            (SUM(t1.AMOUNT)) AS SUM_of_AMOUNT
      FROM WORK.HAVE t1
      GROUP BY t1.CODE,
               t1.CLIENT
      ORDER BY t1.CODE,
               t1.CLIENT;
QUIT;

 

 

Output data (same data, but different order):

CODE CLIENT SUM_of_AMOUNT
M0013R 200 1400
M0013R 201 1800
P9823D 200 2800
P9823D 201 600
T0198K 200 900
U8910E 200 300
U8910E 201 600

 

View solution in original post

7 REPLIES 7
Reeza
Super User

This is a standard PROC MEANS - CLASS or BY statements can be used to group your variables for analysis.

 

proc means data=have noprint nway;
class code client;
var amount;
output out=want sum=total_amount;
run;

proc print data=want;
run;

@LoPez_Diaz wrote:

Hello , 

I'm new to SAS EG , and i'm trying to perform some data cleaning.
I want to sum a column depending on the value of two other columns. 
Here is a scope of the data I have : 

CODE CLIENT AMOUNT
M0013R 201 300
M0013R 201 500
M0013R 201 600
M0013R 200 900
M0013R 200 300
M0013R 201 400
M0013R 200 200
T0198K 200 700
T0198K 200 200
U8910E 201 600
U8910E 200 300
P9823D 200 400
P9823D 200 1800
P9823D 201 600
P9823D 200 600

 

The Code is an alpha-numerical column , 

The CLIENT column can have only 2 values : 200 or 201  .

I want to vertically sum the amount column when the code is the same and the client is the same.

The data I want should look like this : 

CODE CLIENT AMOUNT
M0013R 201 1800
M0013R 200 1400
T0198K 200 900
U8910E 201 600
U8910E 200 300
P9823D 200 2800
P9823D 201 600

 

Thank you in advance for your responses.

 


 

ghosh
Barite | Level 11

In EG Select:

Tasks | Describe | Summary Statistics

 

Drag to the Task Roles:

Amount to the Analysis Variables 

Code and Client to  the Classification Variables

 

Click Run

heffo
Pyrite | Level 9

Or you can open the Query Builder, select the three variables Code, Client and Amount. 

Then on the row for Amount you click on the third column and select sum. And on the bottom of the dialog you see the Select distinct rows only. 

You might want to click on the tab Sort Data to sort them on Code and Client. 

Reeza
Super User

@heffo wrote:

Or you can open the Query Builder, select the three variables Code, Client and Amount. 

Then on the row for Amount you click on the third column and select sum. And on the bottom of the dialog you see the Select distinct rows only. 

You might want to click on the tab Sort Data to sort them on Code and Client. 


That will not sum amount which the OP is doing.

heffo
Pyrite | Level 9

As far as I can see it is the same as the OP had in the post.

 

*Creating the dummy data;
data have;
	input CODE $ CLIENT	AMOUNT;
datalines;
M0013R 201 300
M0013R 201 500
M0013R 201 600
M0013R 200 900
M0013R 200 300
M0013R 201 400
M0013R 200 200
T0198K 200 700
T0198K 200 200
U8910E 201 600
U8910E 200 300
P9823D 200 400
P9823D 200 1800
P9823D 201 600
P9823D 200 600
;;;
run;


*This is the code that the Query Builder gives you when following my instructions;
%_eg_conditional_dropds(WORK.QUERY_FOR_HAVE);

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_HAVE AS 
   SELECT DISTINCT t1.CODE, 
          t1.CLIENT, 
          /* SUM_of_AMOUNT */
            (SUM(t1.AMOUNT)) AS SUM_of_AMOUNT
      FROM WORK.HAVE t1
      GROUP BY t1.CODE,
               t1.CLIENT
      ORDER BY t1.CODE,
               t1.CLIENT;
QUIT;

 

 

Output data (same data, but different order):

CODE CLIENT SUM_of_AMOUNT
M0013R 200 1400
M0013R 201 1800
P9823D 200 2800
P9823D 201 600
T0198K 200 900
U8910E 200 300
U8910E 201 600

 

Reeza
Super User
You don't need distinct when you have a GROUP BY - but you're correct, I missed the SUM portion of your suggestion.
LoPez_Diaz
Obsidian | Level 7
Thank you all for your responses.
Everyone of them worked for me. But I chose this sql procedure , because it offers more flexibility than proc means.
Thank you again !! Have a nice day

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 2459 views
  • 7 likes
  • 4 in conversation