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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.