BRING BACK ALL VALUES FROM TWO DATASETS BUT ONLY IF COMMON VARIABLE IN BOTH

[JC411911]

I may be overthinking this but I hit a block and hoping I can get some guidance. I have two datasets and I need to find the variable member_number that is common in both but also bring back all the other variables. The problem is while some variables are similar there are some that are different. Is this possible or must both datasets have the exact same variables. The first dataset is me.issue_21935_fee and has:

fee_amt

harm_dt

fee_type

tran_seq_nr

chd_origl_eff_dt

init_aud_pask

current_acct_id_nr

member_number

row_number

harm_amount

The next dataset is me.issue_21935_fin_calcs_exclusions:

init_aud_pask

member_number

total_harm_per_account

total_tvm_per_account

total_bdi_per_account

total_owed_per_account

total_owed_per_member

row_number

harm_amount

In reality what I need is to know what member_number appears in both datasets and show the harm amount even if they are similar or different from both datasets. Not sure if it is possible but I would like to create a new variable that shows what dataset each member_number and harm_amount is from.

I tried doing an INTERSECT but that doesn't work unless I just use member_number as it is unlikely there are simimlar harm_amounts in both datasets. This unfortunately doesn't tell me what dataset or what the different harm_amounts are:

proc sql;
	CREATE TABLE COMBINE AS
		select MEMBER_NUMBER,
			HARM_AMOUNT
		from ME.ISSUE_21935_FEE
			INTERSECT
		select MEMBER_NUMBER,
			HARM_AMOUNT
		from ME.ISSUE_21935_FIN_CALCS_EXCLUSIONS;

[Patrick · Accepted solution]

Assuming that member_number is sufficient to define a join condition that doesn't result in a many:many relation below SQL could work.

proc sql;
  create table work.want as
  select 
    t1.member_number as t1_member_number,
    t1.harm_amount as t1_harm_amount,
    t2.member_number as t2_member_number,
    t2.harm_amount as t2_harm_amount
  from 
    ME.ISSUE_21935_FEE t1
    full join
    ME.ISSUE_21935_FIN_CALCS_EXCLUSIONS t2
    on t1.member_number=t2.member_number
    ;
quit;

Ideally provide some sample data created via a working SAS datastep and then show us the desired result.

[JC411911]

Thanks @Patrick that actually gave me the exact results I wanted!