How to filter data based on a condition

[buddha_d]

Dear community,

           I have 2 sample datasets, one is accounts table and other is table with related accounts. I need to join these tables and delete the accounts that are related. I need to have non related account only.

data ACCTS;
infile cards dlm=',' MISSOVER dsd;
input ACT_NBR $ FNAME $ LNAME $ RELATION $ CNAME1 $ CNAME2 $ ;
If CNAME1 NE '' AND CNAME2 NE '' then do;
FULLNAME=STRIP(FNAME)||' '||STRIP(LNAME); 
END;
ELSE DO;
FULLNAME=STRIP(CNAME1)||''||STRIP(CNAME2);
END;
CARDS;
1234,MIKE,MEYERS,JOINT,,   
1234,MARY,MEYERS,JOINT,,
1234,JAY,MEYERS,JOINT-SO,,  
2345,,,,MEYERS,TRUST
;
RUN;



data ACCTS_RELATION;
infile cards dlm=',' MISSOVER dsd;
input ACT_NBR $ FNAME $ LNAME $ RELATION $ CNAME1 $ CNAME2 $ ;

If CNAME1 NE '' AND CNAME2 NE '' then do;
FULLNAME=STRIP(FNAME)||' '||STRIP(LNAME); 
END;
ELSE DO;
FULLNAME=STRIP(CNAME1)||''||STRIP(CNAME2);
END;

CARDS;
1234,MIKE,MEYERS,JOINT,,   
1234,MARY,MEYERS,JOINT,,
1234,JAY,MEYERS,JOINT,, 
1234,SARAH,COOKS,NOREL,,
1234,SHELLY,ROSS,JOINT-DA,,
3456,MIKE,MEYERS,JOINT,,
3456,MARY,MEYERS,JOINT,,
3456,JAY,MEYERS,JOINT-SO,, 
5678,MIKE,MEYERS,JOINT,,
2345,,,,MEYERS,TRUST
7891,,,,MEYERS,TRUST
;
RUN;

I need to have the output that is no relation to the accounts table (eg:1234,SARAH,COOKS,NOREL,,) only one record. I tried to make a full name and join both tables, but not able to get the output. The real data is huge, but I just wanted to get the concept here so that I can implement in bigger tables. 

Thanks in advance.

[Kurt_Bremser]

Please define what a "non-related account" is.

[buddha_d]

The non related account is the record that is not listed as "Owner or Co-owner" of the account 1234

[Kurt_Bremser]

There is nothing in your data that specifies "Owner" or "Co-Owner".

[buddha_d]

Sorry for not mentioning clearly. 

Joint means joint owner 

Joint-so means joint ownership (co-owner is son of the owner)

Joint-da means joint ownership (co-owner is daughter of the owner). Since, it is trust we don't have any one as owner but group of people. 

[maguiremq]

It usually helps if you provide a 'want' data set in a DATALINES program like you did with the two others.

I'm not sure if this captures all of what you want, but it might get you on the right path. You could also join via PROC SQL to avoid the sorting depending on the size of the dataset.

It's also making some assumptions based on duplicates. Nonetheless:

data accts_2 (drop = i);
	set accts;
	array _miss [*] _all_;
	do i = 1 to dim(_miss);
		if missing(_miss[i]) then _miss[i] = "x";
	end;
		id = catx("_", of act_nbr -- cname2);
run;

data accts_relation_2 (drop = i);
	set accts_relation;
	array _miss [*] _all_;
	do i = 1 to dim(_miss);
		if missing(_miss[i]) then _miss[i] = "x";
	end;
		id = catx("_", of act_nbr -- cname2);
run;

/* Editing to include PROC SORT...got dropped between posting. */

proc sort data = accts_2;
by id;
run;

proc sort data = accts_relation_2;
by id;
run;

data want;
	merge
			accts_2				(in = a)
			accts_relation_2	(in = b);
	by
			id;

			if b and not a;

run;

/* To avoid PROC SORT: */

proc sql;
	create table 	want2 as
		select
					t1.*
		from
					accts_relation_2 as t1
						left join
					accts_2 as t2
							on	t1.id = t2.id
		where
					t2.id is null;
quit;

ACT_NBR FNAME LNAME RELATION CNAME1 CNAME2 FULLNAME id 
1234 JAY MEYERS JOINT x x x 1234_JAY_MEYERS_JOINT_x_x 
1234 SARAH COOKS NOREL x x x 1234_SARAH_COOKS_NOREL_x_x 
1234 SHELLY ROSS JOINT-DA x x x 1234_SHELLY_ROSS_JOINT-DA_x_x 
3456 JAY MEYERS JOINT-SO x x x 3456_JAY_MEYERS_JOINT-SO_x_x 
3456 MARY MEYERS JOINT x x x 3456_MARY_MEYERS_JOINT_x_x 
3456 MIKE MEYERS JOINT x x x 3456_MIKE_MEYERS_JOINT_x_x 
5678 MIKE MEYERS JOINT x x x 5678_MIKE_MEYERS_JOINT_x_x 
7891 x x x MEYERS TRUST x 7891_x_x_x_MEYERS_TRUST 

[buddha_d]

Thanks Maguiremq. But, I don't see the result I want to see from the program. Do you need any further clarification? 

[maguiremq]

Hi @buddha_d, it would help if you can provide a DATA step containing a DATALINES statement for what you want the final data set to look like. That way we could match exactly what you want.

Thanks!

[buddha_d]

The output should look like this.

ACT_NBR FNAME LNAME RELATION CNAME1 CNAME2 FULLNAME id
1234 SARAH COOKS NOREL x x x 1234_SARAH_COOKS_NOREL_x_x

[buddha_d]

If there is an account that matches then I don't need to see that in either datasets.

the data I want looks like below

ACT_NBR FNAME LNAME RELATION CNAME1 CNAME2
3456,MIKE,MEYERS,JOINT,,
3456,MARY,MEYERS,JOINT,,
3456,JAY,MEYERS,JOINT-SO,,
5678,MIKE,MEYERS,JOINT,,