SQL Server query connecting through SAS

[tinamiller1]

I am using SAS with SQL Server and I have a query that will just not run. It does not like something about my subquery.

Everything is correct but when It gets to this it errors out.

where (memID,fst_srvc_dt) in
(select distinct
memID,
fst_srvc_dt
from table
where memid>0
and
insured='FI'
and
lob in ('CO', 'SF')

If I take out the where () in and replace it with
where exists
(select distinct

it runs.

but the subquery has to return only those that are
where (memid_fst_srvc_dt) in

I am just not sure how to make it work.

[Rabbit]

You can't do this (memID,fst_srvc_dt). Instead, turn your where clause subquery into an inner join subquery and join on those two fields.

[tinamiller1]

@Rabbit


I am not sure how to do that on the same table. There is only 1 table and I only know how to do joins if I am joining another table to it and then do the whole on this field and that field. Here is my entire query from the start:


proc sql;
connect to odbc(database=oxford);
create table reminder.clms_mrg as
select * from connection to odbc
(select distinct
member_id,
year(earliest_service_date_key) as year,
earliest_service_date_key as fst_srvc_dt,
self_fully_insured as finc_arng_cd,
market as state,
CASE WHEN procedure_code IN ('93510','93511','93524','93526','93508','93452',' 93453','93454','93455','93456','93457',
'93458','93459','93460','93461') THEN 1 END AS CATH,
case when procedure_code in ('33206','33207','33208','33212','33213','33214',' 33225','33240','33249') then 1 end as EP,
case when procedure_code in ('92982','92983','92984','92980','92981','G0290',' G0291','92995','92996') then 1 end as PCI,
sum(allowed_amount) as allw_amt
from oxford2009claims
where (member_id,earliest_service_date_key) in
(select distinct
member_id,
earliest_service_date_key
from oxford2009claims
where
member_id>0
and
procedure_code IN ('93510','93511','93524','93526','93508','93452',' 93453','93454','93455','93456','93457',
'93458','93459','93460','93461','92982','92983','9 2984','92980','92981','G0290','G0291','92995','929 96',
'33206','33207','33208','33212','33213','33214','3 3225','33240','33249')
and
self_fully_insured ='FI'
and
lob_code !='MC')and self_fully_insured = 'FI'
group by member_id,earliest_service_date_key,self_fully_ins ured,procedure_code,market)
order by member_id,fst_srvc_dt,finc_arng_cd,state;
disconnect from odbc;
quit;


Now I cannot do the where () in statement and I am not sure about this inner join to the same table which is called oxford2009claims.

[Rabbit]

That's what you're trying to do? Why do you need a subquery? Just include the where conditions of the subquery in the main query.

[tinamiller1]

I cannot because the procedure codes in the main query are different than the ones in the subquery

[Rabbit]

The procedure codes look the same to me.

[tinamiller1]

@Rabbit
Well the way my boss has her query set up that connects to a ton of differing tables on another server, she pulls all the procedure codes for a group of members. Then she does the where()in statement to take only those members with the results above to pull only those that have claims in the procedure codes that are in the where()in statement. I am not sure if she is doing this because she has to connect to like 12 differing tables or what? So maybe I can get away with not doing a where(in) or subquery. Not sure. Will see what the results are.

[Rabbit]

Well, as far as I can tell, the procedure codes in the subquery are the same as the procedures in the case statements. So if those are the only procedure codes needed, then the subquery is unnecessary.