Case Then Over Partition

[SDasari2]

I am trying to convert the code below into PROC SQL but  the 'over partition' is not recognized

i need to derive the PRIMARY_SVC_DATE from the below logic.

CASE WHEN SLF.FROM_SRVC_DT_KEY <>-99 THEN TO_DATE(SLF.FROM_SRVC_DT_KEY,'YYYY-MM-DD') ELSE
(
(CASE WHEN (CASE CHF.CLM_STMT_FROM_DT_KEY WHEN -99 THEN MIN(SLF.FROM_SRVC_DT_KEY) OVER (PARTITION BY CHED.SRC_CLM_ID)

ELSE (CASE WHEN CHF.CLM_STMT_FROM_DT_KEY >(DECODE(CHF.CLM_STMT_TO_DT_KEY,-99,CHF.CLM_STMT_FROM_DT_KEY,CHF.CLM_STMT_TO_DT_KEY))

THEN MIN(SLF.FROM_SRVC_DT_KEY) OVER (PARTITION BY CHED.SRC_CLM_ID)
WHEN CHF.CLM_STMT_FROM_DT_KEY <= (DECODE( CHF.CLM_STMT_TO_DT_KEY,-99,CHF.CLM_STMT_FROM_DT_KEY, CHF.CLM_STMT_TO_DT_KEY))

THEN CHF.CLM_STMT_FROM_DT_KEY END ) END) = -99
THEN NULL ELSE
TO_DATE((CASE CHF.CLM_STMT_FROM_DT_KEY WHEN -99 THEN MIN(SLF.FROM_SRVC_DT_KEY) OVER (PARTITION BY CHED.SRC_CLM_ID)

ELSE (CASE WHEN CHF.CLM_STMT_FROM_DT_KEY >(DECODE(CHF.CLM_STMT_TO_DT_KEY,-99,CHF.CLM_STMT_FROM_DT_KEY,CHF.CLM_STMT_TO_DT_KEY))

THEN MIN(SLF.FROM_SRVC_DT_KEY) OVER (PARTITION BY CHED.SRC_CLM_ID)
WHEN CHF.CLM_STMT_FROM_DT_KEY <= (DECODE( CHF.CLM_STMT_TO_DT_KEY,-99,CHF.CLM_STMT_FROM_DT_KEY, CHF.CLM_STMT_TO_DT_KEY))

THEN CHF.CLM_STMT_FROM_DT_KEY END ) END), 'YYYY-MM-DD') END)) END AS PRIMARY_SVC_DATE

Thanks for any help you can give me. 

[Tom]

Figure out what it is trying to go and then code it in a different way.

I would say that program is just way to complicated a query, certainly way more complicated that I would try to using SQL.  It probably wouldn't look as complicated if you could write it in a data step code so that you could use normal IF/THEN logic instead of having to use CASE statement.

You cannot use OVER so if you need to calculate values by groups of variable either create tables with the values in advance or code them as sub-queries. Either way join them back to your main table to make them available in your CASE (or IF/THEN) logic.

If you can provide an explanation for what it is trying to do then perhaps someone can suggest easier (or at least easier for humans to read) way to do it.

Provide sample input and output data.

[SDasari2]

Sure Tom...will try to break it up and solve. if not i will provide sample input and out datasets so that you can guide me on how to resolve this.

Actually I was just given the code. I need to figure out what date they are trying to get and i was unable to understand that whole piece i posted.

But will re look into it and give you more details so that you can help me out.

Thanks a lot for your immediate response.

Thank You

[SDasari2]

Here is the sample data. From the 1st 3 dates need to derive the Primary_svc_date. I understood the logic and listed the primary_svc_date, but need help in the writing the code in sql escecially the over/partition by claim_ID

THEN MIN(SLF.FROM_SRVC_DT_KEY) OVER (PARTITION BY CHED.SRC_CLM_ID)

seq_memb_id	SRC_CLM_ID	FROM_SRVC_DT_KEY	CLM_STMT_FROM_DT_KEY	CLM_STMT_TO_DT_KEY	PRIMARY_SVC_DATE
8000000	571931701	-99	20151211	20151211	20151211
8000000	571931701	-99	20151211	20151211	20151211
1000000	571999093	20150616	20150616	20150803	20150616
1000000	571999093	20150624	20150616	20150803	20150624
1000000	571999093	20150617	20150616	20150803	20150617
1000000	571999093	20150619	20150616	20150803	20150619
2000000	571999037	20151222	20151222	20151222	20151222
2000000	571999037	20151222	20151222	20151222	20151222
3000000	575438002	20151211	-99	20151211	20151211
3000000	575438002	20151230	-99	20151211	20151211
3000000	575438002	20151211	-99	20151211	20151211
4000000	575417314	20151222	20151222	-99	20151222
4000000	575417314	20151222	20151222	-99	20151222
4000000	575417314	20151222	20151222	-99	20151222
5000000	575974798	20150616	20150616	20150803	20150616
5000000	575974798	20150623	20150616	20150803	20150616
5000000	575974798	20150624	20150616	20150803	20150616
5000000	575974798	20150625	20150616	20150803	20150616
5000000	575974798	20150618	20150616	20150803	20150616

[Tom]

So that is close to providing us with some data. Better if you post it in a usable form.

data have ;
  infile cards dsd dlm='|' truncover ;
  length seq_memb_id SRC_CLM_ID $ 10
     FROM_SRVC_DT_KEY CLM_STMT_FROM_DT_KEY CLM_STMT_TO_DT_KEY 8
     PRIMARY_SVC_DATE 8
  ;
  informat PRIMARY_SVC_DATE yymmdd.;
  format PRIMARY_SVC_DATE yymmdd10. ;
  input seq_memb_id--PRIMARY_SVC_DATE ;
cards;
8000000|571931701|-99|20151211|20151211|20151211
8000000|571931701|-99|20151211|20151211|20151211
1000000|571999093|20150616|20150616|20150803|20150616
1000000|571999093|20150624|20150616|20150803|20150624
1000000|571999093|20150617|20150616|20150803|20150617
1000000|571999093|20150619|20150616|20150803|20150619
2000000|571999037|20151222|20151222|20151222|20151222
2000000|571999037|20151222|20151222|20151222|20151222
3000000|575438002|20151211|-99|20151211|20151211
3000000|575438002|20151230|-99|20151211|20151211
3000000|575438002|20151211|-99|20151211|20151211
4000000|575417314|20151222|20151222|-99|20151222
4000000|575417314|20151222|20151222|-99|20151222
4000000|575417314|20151222|20151222|-99|20151222
5000000|575974798|20150616|20150616|20150803|20150616
5000000|575974798|20150623|20150616|20150803|20150616
5000000|575974798|20150624|20150616|20150803|20150616
5000000|575974798|20150625|20150616|20150803|20150616
5000000|575974798|20150618|20150616|20150803|20150616
;

Not sure what the -99 mean, so I didn't try to read those "date_key" varaibles as actual dates.

But you probably need to understand what the -99 means since it will have an impact on what you want to use as the MIN() value.

So the to find the min data over id type query in simple old plan vanilla SQLthat would have worked 30 years ago you can code like this.

proc sql ;
  select src_clm_id,MIN(FROM_SRVC_DT_KEY) as min_from_srvc_dt_key
  from have 
  group by SRC_CLM_ID
  ;
quit;

So then you can join that back so you can then just refer to it using the new name.

[SDasari2]

Thanks a lot Tom. I got the result i was expecting.

Thank you so much for assisting me in getting the end result.

[Patrick]

@SDasari2

If you've got a solution then please mark the answer as solution.