topic Re: How to filter data based on a condition in SAS Programming

How to filter data based on a condition

buddha_d — Wed, 15 Sep 2021 12:02:17 GMT

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.

Re: How to filter data based on a condition

Kurt_Bremser — Wed, 15 Sep 2021 12:10:28 GMT

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

Re: How to filter data based on a condition

buddha_d — Wed, 15 Sep 2021 12:12:25 GMT

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

Re: How to filter data based on a condition

Kurt_Bremser — Wed, 15 Sep 2021 12:14:48 GMT

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

Re: How to filter data based on a condition

maguiremq — Wed, 15 Sep 2021 12:43:17 GMT

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

Re: How to filter data based on a condition

buddha_d — Wed, 15 Sep 2021 16:33:24 GMT

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.

Re: How to filter data based on a condition

buddha_d — Wed, 15 Sep 2021 16:35:17 GMT

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

Re: How to filter data based on a condition

maguiremq — Wed, 15 Sep 2021 17:13:11 GMT

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!

Re: How to filter data based on a condition

buddha_d — Wed, 15 Sep 2021 17:19:07 GMT

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

Re: How to filter data based on a condition

buddha_d — Fri, 17 Sep 2021 00:07:40 GMT

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,,