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 Group | METHOD | Amount |
Unprotected | % | 10 |
Protected | Flat | 10 |
Protected | Flat | 10 |
Protected | Flat | 10 |
Protected | Flat | 10 |
Protected | Flat | 15 |
Unprotected | % | 15 |
Protected | Flat | 15 |
Protected | Flat | 20 |
Protected | % | 50 |
Protected | % | 100 |
Table B
Active vs. Expired | Security Group | METHOD | Amount |
Active | Unprotected | % | 10 |
Active | Protected | Flat | 10 |
Expired | Protected | Flat | 10 |
Expired | Protected | Flat | 10 |
Expired | Protected | Flat | 10 |
Active | Protected | Flat | 15 |
Active | Unprotected | % | 15 |
Expired | Protected | Flat | 15 |
Active | Protected | Flat | 20 |
Expired | Protected | % | 50 |
Active | Protected | % | 100 |
The desired result for Table C:
Active vs. Expired | Security Group | METHOD | Amount |
Active | Unprotected | % | 20 |
Active | Protected | Flat | 20 |
Expired | Protected | Flat | 20 |
Expired | Protected | Flat | 20 |
Expired | Protected | Flat | 20 |
Active | Protected | Flat | 30 |
Active | Unprotected | % | 30 |
Expired | Protected | Flat | 30 |
Active | Protected | Flat | 40 |
Expired | Protected | % | 100 |
Active | Protected | % | 200 |
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;
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;
THANK YOU!!! 🙂
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.