topic Re: How to cross check 2 variables based on condition when joining 2 tables. in SAS Programming

How to cross check 2 variables based on condition when joining 2 tables.

Banana19 — Tue, 08 Mar 2022 13:20:34 GMT

Hi All - As the subject line says I've come across a scenario where I need to cross check variables from 2 different tables based on a condition. I wrote a case when expression but I do not think it works. Below is the example of logic I'm trying to use.

CASE WHEN (COMPRESS(A.Account_Number)=COMPRESS(B.Account_Number)) AND A.name NOT IN B.name THEN '1'
END AS FLAG

In the above example, when Table A account number equals to Table B account number then the name variable present in table A should cross check against name variable of table B for that particular account number. If the name variable in Table A is not present in name variable of Table B for that particular account then I'm creating a flag. This needs to performed for when ever both tables account numbers match.

It would be great if you could help me in this issue.

Thanks,

Re: How to cross check 2 variables based on condition when joining 2 tables.

PaigeMiller — Tue, 08 Mar 2022 13:33:23 GMT

Why do you think it doesn't work? Explain.

Typically, these types of comparisons to get matching account_number values is done in a JOIN rather than in a CASE statement, something like this (partial code)

proc sql;
    create table want as select a.account_number, /* any other variables you want go here */, 
    case when a.name not in b.name then 1 end as flag
    from a left join b on
        compress(a.account_number)=compress(b.account_number)

I don't really know about the part a.name not in b.name, that doesn't feel right to me either, state clearly what you are trying to achieve with this part of the code

But you really need to take a step back and instead of looking at this as a case statement issue and explaining it that way, just explain the desired output

Re: How to cross check 2 variables based on condition when joining 2 tables.

Banana19 — Tue, 08 Mar 2022 15:59:53 GMT

Hi Paige, Thank you for responding. Here is how my data looks like and my desired output.

TABLE A
Account# Name
A11 Jon A.
A11 Jon A.
A11 Mike B.
A12 Tom C.

A12 Tom C.

A13 Ed

TABLE B
Account# Name
A11 Jon A.
A11 Jane
A11 Mike B.
A12 Vira

A12 Tommy

A13 Ed

A13 Charles

A13 Scott

EXPECTED OUTPUT(TABLE C)
Account# FLAG
A11 1
A12 0

A13 1

The expected output is table C. The account# A11 has flag 1 because we have names(JON A., MIKE B.) in common for both tables A/B. I flagged 0 for account# A12 because we do not have common names in both tables A/B for this particular account#.

So i was wondering how to approach the issue.

Hope this helps.

Re: How to cross check 2 variables based on condition when joining 2 tables.

maguiremq — Tue, 08 Mar 2022 17:24:33 GMT

Try this:

data a;
infile datalines delimiter = ',' dsd truncover;
input account :$3. name :$10.;
datalines;
A11,Jon A.
A11,Jon A.
A11,Mike B.
A12,Tom C.
A12,Tom C.
A13,Ed
;
run;

data b;
infile datalines delimiter = ',' dsd truncover;
input account :$3. name :$10.;
datalines;
A11,Jon A.
A11,Jane
A11,Mike B.
A12,Vira
A12,Tommy
A13,Ed
A13,Charles
A13,Scott
;
run;

proc sql;
	select
				distinct t1.account,
				case when catx(',', t1.account, t1.name) = catx(',', t2.account, t2.name) then 1 else 0 end as flag
	from
				a as t1
					left join
				b as t2
					on	catx(',', t1.account, t1.name) = catx(',', t2.account, t2.name);
quit;

May be a better way but that works for me.

Re: How to cross check 2 variables based on condition when joining 2 tables.

FreelanceReinh — Tue, 08 Mar 2022 18:26:33 GMT

Hi @Banana19,

So you want to get one observation per matching account number? Then, using the definition of the flag from your second post (i.e., flag=1 if at least one common name is found), I'd suggest an inner join with a GROUP BY clause:

data have_a;
input account $ name &$10.;
cards;
A10       Rahul
A11       Jon A.
A11       Jon A.
A11       Mike B.
A12       Tom C.
A12       Tom C.
A13       Ed
A13       John Doe
;

data have_b;
input account $ name &$10.;
cards;
A11       Jon A.
A11       Jane
A11       Mike B.
A12       Vira
A12       Tommy
A13       Ed
A13       Charles
A13       Scott
A14       Reinhard
;

proc sql;
create table want as
select a.account, max(a.name=b.name) as flag
from have_a a join have_b b
on a.account=b.account
group by a.account;
quit;

I've added a few records to the sample data so that different types of joins would create different results. Note that account A13 (with the new "John Doe" record added) still occurs only once in the output dataset.

Re: How to cross check 2 variables based on condition when joining 2 tables.

Ksharp — Wed, 09 Mar 2022 11:53:28 GMT

data have_a;
input account $ name &$10.;
cards;
A10       Rahul
A11       Jon A.
A11       Jon A.
A11       Mike B.
A12       Tom C.
A12       Tom C.
A13       Ed
A13       John Doe
;

data have_b;
input account $ name &$10.;
cards;
A11       Jon A.
A11       Jane
A11       Mike B.
A12       Vira
A12       Tommy
A13       Ed
A13       Charles
A13       Scott
A14       Reinhard
;

proc sql;
create table want as
select a.account, count(b.name) ne 0 as flag
from have_a a left join have_b b 
on a.account=b.account and a.name=b.name
group by a.account;
quit;