Help using Base SAS procedures

counting matches - need help

Reply
N/A
Posts: 0

counting matches - need help

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
Super Contributor
Posts: 260

Re: counting matches - need help

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
N/A
Posts: 0

Re: counting matches - need help

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
Super Contributor
Posts: 260

Re: counting matches - need help

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
Ask a Question
Discussion stats
  • 3 replies
  • 130 views
  • 0 likes
  • 2 in conversation