Jeff, did you misplace a '('?? Also, aliases are not necessary within IN()
try..

WHERE (a.ssno NOT IN (SELECT ssno FROM helth_priors_tmp)) ;
AND (a.ssno NOT IN (SELECT ssno FROM helth_currents_tmp))


....

or, if that doesn't work...
run it the way it is now then..

SELECT * FROM cHelth_tmp3 ;
WHERE ssno NOT IN (SELECT ssno FROM helth_currents_tmp);
INTO cursor cHelth_tmp4

HTW

Matt