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.
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;
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;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.