topic Re: Match 2 out of 3 conditions? in SAS Programming

Match 2 out of 3 conditions?

ybz12003 — Fri, 07 Feb 2020 22:40:31 GMT

proc sql;
	create table want as 
	select lab as a
	       park as b
	from a b 
	where ((a.first_name=b.first_name and a.last_name=b.last_name)+
			(a.first_name=b.first_name and a.birthdate=b.DOB)+
	         (a.last_name=b.last_name and b.DOB)) >1 ;
quit;

Hello,

I have a dataset A with 50 variables with 2000 rows, dataset B with 10 variables with 300 rows. Both dataset have same name, birthdate and sex variables. As long as these two dataset have same (name and birthdate) or same (sex and name) or same (sex and birthday) have same value, I link those data together first. Thank you.

Re: Match 2 out of 3 conditions?

ChrisNZ — Fri, 07 Feb 2020 22:02:26 GMT

Like this?

proc sql;
  select A.*
  from A,B
  where (A.NAME=B.NAME and A.DATE = B.DATE)
     or (A.NAME=B.NAME and A.SEX  = B.SEX )
     or (A.DATE=B.DATE and A.SEX  = B.SEX );

Re: Match 2 out of 3 conditions?

ybz12003 — Fri, 07 Feb 2020 22:08:05 GMT

I use VERY similar code, some how the result dataset only comes with dataset A variables, no dataset B variables.

Re: Match 2 out of 3 conditions?

Tom — Fri, 07 Feb 2020 22:09:21 GMT

SAS evaluates boolean expressions as 1 or 0. So just sum them.

where sum( a.name=b.name, a.bday=b.bday, a.sex=b.sex )  > 1

Re: Match 2 out of 3 conditions?

Tom — Fri, 07 Feb 2020 22:11:14 GMT

A dataset can only one variable of a given name. So if you do select a.NAME,b.NAME you don't get two name variables, just one. In particular PROC SQL will pick the first one and ignore the second one. You need to give them unique names.

Re: Match 2 out of 3 conditions?

ybz12003 — Fri, 07 Feb 2020 22:16:47 GMT

Error: Summary functions are restricted to the SELECT and HAVING clauses only

Re: Match 2 out of 3 conditions?

SASKiwi — Fri, 07 Feb 2020 22:19:30 GMT

I would advise joining by all three conditions first as that is the most reliable. The next most reliable would be birth date and name followed by sex and name as you have described. SQL is a good choice for this:

proc sql;
  create table want as
  select  A.name
            ,A.birthdate
            ,A.sex
            ,B.name
            ,B.birthdate
            ,B.sex
            ,'1 Name, Birthdate, Sex' as jointype length = 20
  from A
  left join B
  on A.name = B.name
  and A.birthdate = B.birthdate
  and A.sex = B.sex
  union all
  select  A.name
            ,A.birthdate
            ,A.sex
            ,B.name
            ,B.birthdate
            ,B.sex
            ,'2 Name, Birthdate' as jointype length = 20
  from A
  left join B
  on A.name = B.name
  and A.birthdate = B.birthdate
  union all
  select  A.name
            ,A.birthdate
            ,A.sex
            ,B.name
            ,B.birthdate
            ,B.sex
            ,'3 Name, Sex' as jointype length = 20
  from A
  left join B
  on A.name = B.name
  and A.sex = B.sex
  order by Name, jointype
  ;
quit;

You will end up with multiple join types for each name so then just choose the one with the lowest number.

Re: Match 2 out of 3 conditions?

ChrisNZ — Fri, 07 Feb 2020 22:20:52 GMT

Try using the + sign instead of the sum function.

SQL is pickier than a data step,

Otherwise, try my syntax.

Re: Match 2 out of 3 conditions?

ybz12003 — Fri, 07 Feb 2020 22:22:45 GMT

I still only get set 1 variables.

Re: Match 2 out of 3 conditions?

Tom — Fri, 07 Feb 2020 22:24:21 GMT

@ybz12003 wrote:

Error: Summary functions are restricted to the SELECT and HAVING clauses only

Don't use SQL aggregate functions. Use the SAS sum(,) function like in the code I posted. Or just type the + operators instead.

Re: Match 2 out of 3 conditions?

ybz12003 — Fri, 07 Feb 2020 22:26:37 GMT

sum didn't help,

Re: Match 2 out of 3 conditions?

Tom — Fri, 07 Feb 2020 22:30:38 GMT

select a.name as nameA
     , b.name as nameB
...

Re: Match 2 out of 3 conditions?

ybz12003 — Fri, 07 Feb 2020 22:32:08 GMT

I have been trying use sum or +, I still only get dataset 1 variables. Not including dataset 2 variables.

Re: Match 2 out of 3 conditions?

ybz12003 — Fri, 07 Feb 2020 22:41:54 GMT

Could someone let me know where my code went wrong, I keep getting dataset a variables. That's it. Where is the set b?

Re: Match 2 out of 3 conditions?

Tom — Sat, 08 Feb 2020 01:58:04 GMT

Post the code. Or Post the lines from the SAS log. Make sure to use the Insert Code or Insert SAS Code button.

Your original code was only referencing one dataset named A but assigning the alias B to it because you wrote

from A B
where ...

To see that type of mistake in action try this code:

data a b; 
  set sashelp.class; 
  if sex='M' then output a ;
  else output b;
run;

proc sql ;
select a.*,b.*
from a b
;
quit;

Instead use

from A , B
where ...

from A 
inner join B
on ....

Re: Match 2 out of 3 conditions?

ballardw — Fri, 07 Feb 2020 23:50:41 GMT

@ybz12003 wrote:

Error: Summary functions are restricted to the SELECT and HAVING clauses only

Since your example code at the start of this thread did not include any summary functions then perhaps it is time to show the actual code you are using when generating errors.

Re: Match 2 out of 3 conditions?

ChrisNZ — Sat, 08 Feb 2020 01:27:53 GMT

> Could someone let me know where my code went wrong, I keep getting dataset a variables. That's it. Where is the set b?