counting matches - need help

[deleted_user]

Pls Help!

I have 2 datasets: Dataset A has a list of names and dobs from a membership registry. Dataset B has a list of names and dobs from a testing registry (lab results). We want to know how many (not who) of those listed in dataset A are found in Dataset B. (Hopefully no output file that links due to confidential nature of linking the names to the test file). Is there a way to just get counts of how many from A were found in B?

cheers,

Maggie

[Olivier]

Hi Maggie.
Once again, SQL is your friend for that kind of problem (ID is a variable that is found in both A and B datasets, but the name can vary from a dataset to another, just change in the syntax below) :
[pre]
PROC SQL ;
SELECT COUNT(*) AS howMany
FROM a
WHERE a.id IN (SELECT b.id FROM b)
;
QUIT ;
[/pre]
Regards.
Olivier

[deleted_user]

Thank so much Olivier,
I will give this a go. Just another question if you don't mind. I wish we just had a simple study ID but since it will be based on a perfect match of both name and dob (will be strictly formated), do you think there could be any snags when it is rewritten to reflect that?

Maggie Message was edited by: maggie

[Olivier]

No problem for that. Just change the WHERE part of the query to create (just during the execution of the query) a unique variable that is the concatenation of both dob and name... Just make sure the DOB variable have the same length in both tables, to avoid problems with trailing blanks (otherwise apply TRIM() function to the DOB variable in the query).
[pre]
PROC SQL ;
SELECT COUNT(*) AS howMany
FROM a
WHERE a.dob!!"/"!!a.name IN (SELECT b.dob!!"/"!!b.name FROM b)
/* or TRIM(a.dob)!!"/"!!a.name etc. */
;
QUIT ;
[/pre]
Olivier