PROC SQL how to add 2 columns from 2 different tables

sufiya — Tue, 24 Apr 2018 02:34:40 GMT

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

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

ChrisNZ — Tue, 24 Apr 2018 02:42:45 GMT

You can't do this using proc sql as it doesn't necessarily read tables sequentially. You need a data step here.

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

PGStats — Tue, 24 Apr 2018 03:48:47 GMT

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;

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

sufiya — Tue, 24 Apr 2018 14:34:15 GMT

THANK YOU!!! 🙂

topic PROC SQL how to add 2 columns from 2 different tables in SAS Programming

PROC SQL how to add 2 columns from 2 different tables

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

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

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