ABSTRACT
PROC SQL keeps all records in dataset A and ignores the condition on dataset A. As an option, apply a WHERE dataset option to first subset dataset A.
7. Is it possible to select all variables in a dataset as well as apply the COALESCE() function to keep nonmissing values in the same SELECT statement when joining two tables? In general, yes; make sure to first apply the COALESCE() function before selecting all variables to correctly define the possible nonmissing variable. Switching the order may not work. SELECT UNIQUE COALESCE(a.dosegrp, c.dosegrp) AS dosegrp, A.* FROM conmeds_all2 AS A LEFT JOIN analyvar AS C ON A.subjid = C.subjid ORDER BY dose, scrid; As an alternative to preventing the WARNING message, save to a new variable name, such as B_DOSEGRP, and then apply DROP DOSEGRP and RENAME B-DOSEGRP to DOSEGRP as dataset options.
