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;
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
@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?
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
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.