BookmarkSubscribeRSS Feed
SDasari2
Calcite | Level 5

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. 

6 REPLIES 6
Tom
Super User Tom
Super User

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
Calcite | Level 5

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
Calcite | Level 5

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_idSRC_CLM_IDFROM_SRVC_DT_KEYCLM_STMT_FROM_DT_KEYCLM_STMT_TO_DT_KEYPRIMARY_SVC_DATE
8000000571931701-99201512112015121120151211
8000000571931701-99201512112015121120151211
100000057199909320150616201506162015080320150616
100000057199909320150624201506162015080320150624
100000057199909320150617201506162015080320150617
100000057199909320150619201506162015080320150619
200000057199903720151222201512222015122220151222
200000057199903720151222201512222015122220151222
300000057543800220151211-992015121120151211
300000057543800220151230-992015121120151211
300000057543800220151211-992015121120151211
40000005754173142015122220151222-9920151222
40000005754173142015122220151222-9920151222
40000005754173142015122220151222-9920151222
500000057597479820150616201506162015080320150616
500000057597479820150623201506162015080320150616
500000057597479820150624201506162015080320150616
500000057597479820150625201506162015080320150616
500000057597479820150618201506162015080320150616
Tom
Super User Tom
Super User

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
Calcite | Level 5

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

 

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

Patrick
Opal | Level 21

@SDasari2

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1207 views
  • 0 likes
  • 3 in conversation