BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shlomiohana
Obsidian | Level 7

Hello,

 

I have 2 tables:

 

Table A:

Branch_IdAreaCard_groupNum_issued
171220Isracard Group3
172221Leumi Group22
172221Isracard Group4
173275ICC Group15

 

Table B:

Branch_IdAreaCard_groupNum_issued
171220Isracard Group1237
172223Leumi Group12
172221Isracard Group4
14199ICC Group211

 

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_IdAreaCard_groupNum_issued
171220Isracard Group3
172221Leumi Group22
172221Isracard Group4
172223Leumi Group12
173275ICC Group15
14199ICC Group221

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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;
PG

View solution in original post

4 REPLIES 4
PGStats
Opal | Level 21

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;
PG
Angel_Larrion
SAS Employee

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;
mkeintz
PROC Star

@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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
ChrisNZ
Tourmaline | Level 20

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;

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 628 views
  • 2 likes
  • 5 in conversation