topic Re: How do i adds records to an existing table in SAS Programming

How do i adds records to an existing table

shlomiohana — Wed, 12 Aug 2020 21:05:45 GMT

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.

Re: How do i adds records to an existing table

PGStats — Wed, 12 Aug 2020 21:38:58 GMT

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;

Re: How do i adds records to an existing table

Angel_Larrion — Thu, 13 Aug 2020 03:52:10 GMT

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;

Re: How do i adds records to an existing table

ChrisNZ — Thu, 13 Aug 2020 04:38:37 GMT

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;

Re: How do i adds records to an existing table

mkeintz — Thu, 13 Aug 2020 08:10:39 GMT

@Angel_Larrion

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.