I'd like to understand the likely cause for 0 row after executing the aggregate query below. Subquery after first IN yields some rows but outer query is producing 0 rows. Ignore SAS syntax error please.
proc sql;
CREATE TEMP TABLE TEMP_RM_DUP_MEMBER_ADDL AS
SELECT (scan(merge_key,1,"-" )) as T_CLIENT_ID,
MAX(scan(merge_key,2,"-" )) as PT_MEDICARE_ID,
(scan(merge_key,3,"-" )) as SUB_GROUP_POLICY_NUM,
(scan(merge_key,4,"-" )) as PT_POLICY_START_DT,
(scan(merge_key,5,"-" )) as PT_POLICY_END_DTFROM FORM.MEMBER_addl
WHERE ((scan(merge_key,1,"-" )),
(scan(merge_key,2,"-" )),
(scan(merge_key,4,"-" )),
(scan(merge_key,5,"-" )))
IN
(
SELECT (scan(merge_key,1,"-" )) as PT_CLIENT_ID ,
MAX(scan(merge_key,3,"-" )) as SUB_GROUP_POLICY_NUM,
(scan(merge_key,4,"-" )) as PT_POLICY_START_DT,
(scan(merge_key,5,"-" )) as PT_POLICY_END_DT
FROM FORM.MEMBER_addl
WHERE ((scan(merge_key,1,"-" )),
(scan(merge_key,4,"-" )),
(scan(merge_key,5,"-" )))
IN
(
SELECT (scan(merge_key,1,"-" )) as PT_CLIENT_ID,
MAX(scan(merge_key,4,"-" )) as PT_POLICY_START_DT,
(scan(merge_key,5,"-" )) as PT_POLICY_END_DT
FROM FORM.MEMBER_addl
WHERE ((scan(merge_key,1,"-" )),
(scan(merge_key,5,"-" )) )
IN
(
SELECT (scan(merge_key,1,"-" )) as PT_CLIENT_ID,
MAX(scan(merge_key,5,"-" )) as PT_POLICY_END_DT
FROM (select * from FORM.MEMBER_addl
WHERE (scan(merge_key,1,"-" ))
IN
(
SELECT MEMBER_UMI_PIN
FROM raw.employee
WHERE optum_correlation_id = 199341349 AND MEMBER_UMI_PIN = 17654001001 ))
GROUP BY (scan(merge_key,1,"-" )))
GROUP BY (scan(merge_key,1,"-" )), (scan(merge_key,5,"-" )))
GROUP BY (scan(merge_key,1,"-" )), (scan(merge_key,4,"-" )), (scan(merge_key,5,"-" ))
)
GROUP BY (scan(merge_key,1,"-" )), (scan(merge_key,3,"-" )), (scan(merge_key,4,"-" )), (scan(merge_key,5,"-" )) ;
quit;
To summarize the issue, below query is producing 0 row in the result what would be the cause for the issue?
proc sql;
CREATE TEMP TABLE TEMP_RM_DUP_MEMBER_ADDL AS
SELECT (scan(merge_key,1,"-" )) as T_CLIENT_ID,
MAX(scan(merge_key,2,"-" )) as PT_MEDICARE_ID,
(scan(merge_key,3,"-" )) as SUB_GROUP_POLICY_NUM,
(scan(merge_key,4,"-" )) as PT_POLICY_START_DT,
(scan(merge_key,5,"-" )) as PT_POLICY_END_DTFROM FORM.MEMBER_addl
WHERE ((scan(merge_key,1,"-" )),
(scan(merge_key,2,"-" )),
(scan(merge_key,4,"-" )),
(scan(merge_key,5,"-" )))
IN
(
/*subqueries here producing results*/
)
GROUP BY (scan(merge_key,1,"-" )), (scan(merge_key,3,"-" )), (scan(merge_key,4,"-" )), (scan(merge_key,5,"-" )) ;
quit;
... View more
