Hello,
I have 2 tables:
Table A:
Branch_Id | Area | Card_group | Num_issued |
171 | 220 | Isracard Group | 3 |
172 | 221 | Leumi Group | 22 |
172 | 221 | Isracard Group | 4 |
173 | 275 | ICC Group | 15 |
Table B:
Branch_Id | Area | Card_group | Num_issued |
171 | 220 | Isracard Group | 1237 |
172 | 223 | Leumi Group | 12 |
172 | 221 | Isracard Group | 4 |
14 | 199 | ICC Group | 211 |
I want that all the records in table A to appear in the final table plus the records in table B when the 3 fields (Branch_Id + Area + Card_group) do not exactly match to table A I want to Add the record of table B to table A.
If there is a match in 3 fields and only the field Num_issued is different, the record of table B will not be taken.
Example of the desired table.
Final table:
Branch_Id | Area | Card_group | Num_issued |
171 | 220 | Isracard Group | 3 |
172 | 221 | Leumi Group | 22 |
172 | 221 | Isracard Group | 4 |
172 | 223 | Leumi Group | 12 |
173 | 275 | ICC Group | 15 |
14 | 199 | ICC Group | 221 |
Thanks.
Doing this with SQL:
proc sql;
create table TC as
select *
from TB as a
where not exists (select * from TA where branch_id=a.branch_id and area=a.area and card_group=a.card_group);
insert into TA select * from TC;
drop table TC;
quit;
Doing this with SQL:
proc sql;
create table TC as
select *
from TB as a
where not exists (select * from TA where branch_id=a.branch_id and area=a.area and card_group=a.card_group);
insert into TA select * from TC;
drop table TC;
quit;
Try this:
data table_a;
set table_a;
table=1;
run;
data table_b;
set table_b;
table=2;
run;
data pre_want;
set table_a table_b;
run;
proc sort data=pre_want;
by Branch_Id Area Card_group table;
run;
data want;
set pre_want;
by Branch_Id Area Card_group ;
if first.card_group=1 then output;
drop table;
run;
You don't actually need to create intermediate files with the TABLE variable. All you have to do is append table2 to table1. Then you can tell proc sort to place all table1 records prior to tied table2 records. It's called the EQUALS option in proc sort.
data preneed /view=preneed;
set table_a table_b;
run;
proc sort data=preneed out=need EQUALS;
by branch_id area card_group;
run;
data want;
set need;
by branch_id area card_group;
if first.card_group;
run;
I specified EQUALS in the proc sort above (as opposed to NOEQUALS). But unless your system administrator did something pathological when installing SAS, it is the default for proc sort, so the option doesn't need to be specifically entered.
If both tables are sorted, then a MERGE is all you need:
data WANT;
merge B A; * The order matters: Keep A values rather than B values if both are found;
by Branch_Id Area Card_group;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.