DATA Step, Macro, Functions and more

PROC SQL how to add 2 columns from 2 different tables

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

PROC SQL how to add 2 columns from 2 different tables

Hello,

I want to add/sum up and merge two columns that are in 2 different tables. What is the best logic/method that would be used for this type of situation?

 

For example, I want to join Table A & B and merge the 'Amount' column and sum up the numbers. 

 

Table A

Security GroupMETHODAmount
   
Unprotected%10
ProtectedFlat10
ProtectedFlat10
ProtectedFlat10
ProtectedFlat10
ProtectedFlat15
Unprotected%15
ProtectedFlat15
ProtectedFlat20
Protected%50
Protected%100

 

 

Table B

Active vs. ExpiredSecurity GroupMETHODAmount
    
ActiveUnprotected%10
ActiveProtectedFlat10
ExpiredProtectedFlat10
ExpiredProtectedFlat10
ExpiredProtectedFlat10
ActiveProtectedFlat15
ActiveUnprotected%15
ExpiredProtectedFlat15
ActiveProtectedFlat20
ExpiredProtected%50
ActiveProtected%100

 

The desired result for  Table C:

Active vs. ExpiredSecurity GroupMETHODAmount
    
ActiveUnprotected%20
ActiveProtectedFlat20
ExpiredProtectedFlat20
ExpiredProtectedFlat20
ExpiredProtectedFlat20
ActiveProtectedFlat30
ActiveUnprotected%30
ExpiredProtectedFlat30
ActiveProtectedFlat40
ExpiredProtected%100
ActiveProtected%200

Accepted Solutions
Solution
4 weeks ago
Esteemed Advisor
Posts: 5,390

Re: PROC SQL how to add 2 columns from 2 different tables

If you are absolutely certain that the tables match, line per line, simply do this:

 

data c;
set a;
set b(rename=amount=extra);
amount = amount + extra;
drop extra;
run;
PG

View solution in original post


All Replies
PROC Star
Posts: 2,211

Re: PROC SQL how to add 2 columns from 2 different tables

You can't do this using proc sql as it doesn't necessarily read tables sequentially. You need a data step here.
Solution
4 weeks ago
Esteemed Advisor
Posts: 5,390

Re: PROC SQL how to add 2 columns from 2 different tables

If you are absolutely certain that the tables match, line per line, simply do this:

 

data c;
set a;
set b(rename=amount=extra);
amount = amount + extra;
drop extra;
run;
PG
Contributor
Posts: 34

Re: PROC SQL how to add 2 columns from 2 different tables

THANK YOU!!! Smiley Happy

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 110 views
  • 1 like
  • 3 in conversation