COALESCE functionIn our administrative tables, the Social Security Number (SSN) field is not very well populated We need to go after different sources:.. Membership tables (old, current, and daily) and other utilization tables PROC SQL makes the selection process very Easy, WHERE The coalesce Function Will Pick The First Non-missing value.proc SQL; Create Table_ssninfo asselect S. *, Coalesce (C.ssn, A.ssn, B.ssn, D.ssn) AS SSN, from _Sample AS S Left Join _cms as a on s.hrn = a.hrnleft join _mg as b on s.hrn = b.hrnleft join _cmsdl as c on s.hrn = c.hrnleft join _docplus as d on s.hrn = d.hrn ; Quit;
Access other databasesBelow is SQL code to query an Oracle table from PC SAS. (OO.CMS_MEMBER is our membership file with 400,000 rows). PROC SQL is the only way you can join a SAS table and an Oracle table. Method 1 uses join While Method 2 Uses Subquery. As we can see from the chart on the next page method 2 is super result for a small sample.d 1 -; * - Methoproc SQL; Create Table TTT Assread B.hrn, Famact, ReltnFrom Xsample As a inner join oscms_member (dbkey = hrn dbindex = yes) as bon a.hrn = b.hrn; quit; * - method 2 -; proc SQL NOPRINT; SELECT HRN INTO: HRN_LIST SEPARATED BY ',' FROM xsample; QUIT; PROC SQL; CREATE TABLE TTT ASSELECT HRN, FAMACT, RELTNFROM OO.CMS_MEMBER (dbkey = hrn dbindex = yes) WHERE HRN IN (& HRN_LIST); QUIT; Note: The maximum for & HRN_LIST under Windows SAS version 8, for 8 Digits HRN, IS ABOUT 1000 HRNS; in Other Words, The SQL LIMIT COMES Before SAS
