Help using Base SAS procedures

Case Then Over Partition

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Case Then Over Partition

I am trying to convert the code below into PROC SQL but am stuck. The 'over partition' is not recognized by PROC SQL.  Any help would be greatly appreciated.

The output would look as follows for one patient:

ID_PATIENT    DOS_FM           DOS_TO           ID_PROV          PS        ICN            FLG      EPISODE

100001              1/6/2011            1/12/2011        11002                02        1011             1          1

100001              1/12/2011          1/15/2011        11002                06         1012            0          1

100001              1/20/2011           1/28/2011        11002                51        1013            1          2

select

    a.*,

    sum(ep_flg)over(partition by id_patient order by dos_from_dt) episode

from

    (

    select

        id_patient, dos_from_dt, dos_to_dt, id_provider_billing, dsc_patient_status, num_icn,

        case when dos_from_dt-lag(dos_to_dt)over(partition by id_patient order by dos_from_dt) = 0 then 0 else 1 end ep_flg

    from claim_business cb

    where dos_from_dt between '01-jan-2011' and '31-jan-2011'

    ) a

Thanks for any help you can give me.  I am a newbie. 


Accepted Solutions
Solution
‎10-18-2012 04:28 PM
Super User
Super User
Posts: 6,495

Re: Case Then Over Partition

Can you translate that into English?

Looks like you want to create an episode variable.

data want ;

   set claim_business;

   by id_patient dos_from_dt ;

   prev_to = lag(dos_to_dt);

   if first.id_patient then episode=1;

   else if dos_from_dt  - prev_to > 1 then episode+1;

run;

View solution in original post


All Replies
Solution
‎10-18-2012 04:28 PM
Super User
Super User
Posts: 6,495

Re: Case Then Over Partition

Can you translate that into English?

Looks like you want to create an episode variable.

data want ;

   set claim_business;

   by id_patient dos_from_dt ;

   prev_to = lag(dos_to_dt);

   if first.id_patient then episode=1;

   else if dos_from_dt  - prev_to > 1 then episode+1;

run;

New Contributor
Posts: 2

Re: Case Then Over Partition

Tom:

Many thanks.  You captured what I wanted right on the nose.  I can't tell you how appreciative I am.  If you are ever in the Boston area I will take you for a nice Italian dinner.


☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 7467 views
  • 1 like
  • 2 in conversation