topic Re: counting matches - need help in SAS Procedures

counting matches - need help

deleted_user — Sat, 28 Jun 2008 14:58:38 GMT

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

Re: counting matches - need help

Olivier — Sat, 28 Jun 2008 16:49:14 GMT

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

Re: counting matches - need help

deleted_user — Sat, 28 Jun 2008 20:16:50 GMT

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

Re: counting matches - need help

Olivier — Tue, 01 Jul 2008 08:59:23 GMT

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