Query producing 0 row after having records in subquery

[David_Billa]

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;

[yabwon]

1) Did you look int the LOG ? Usually it helps to figure out problems.

2) Why you assume that from the fact that a sub-query returns value the main query has to return something, See example:

data class;
  set sashelp.class;
  where age ne 13;
  ;
run;

proc sql; 
/* sub-query */
select age from sashelp.class where age = 13

/* query */
select * from class
where age in
(
select age from sashelp.class where age = 13
);
quit

3) You wrote " Ignore SAS syntax error please" but the code is syntactically incorrect, something like:

WHERE (age,sex) 
  IN 
  (select age,sex from sashelp.class where sex="F" and age in (12,13) )

is "wrong code". BTW the: 

CREATE  TEMP TABLE

is wrong too. 

Bart

[David_Billa]

@yabwon  I agree with you. But still I want to know what would be likely cause for 0 rows in the outer query? Any issue with Where clause or last group by?

[yabwon]

The IN operator has the following syntax:

where A_VARIABLE in (A_LIST - from sub-query)

Query does not return anything, even when sub-query does, probably because the list of values in "A_LIST" provided by sub-query does not match with ANY value in the "A_VARIABLE" variable...

1) variable="A" list is ("B" "C" "D") 

2) variable="A" list is ("a" "B" "C")

3) variable="  A" list is ("A "B" "C")

Bart