PostgreSQL code translate into FEDSQL or SAS

[Zula]

Hi. I would like to translate postgreSQL below code into SAS or FEDSQL. Can anyone help? Thank you.

select
icustay_id
, charttime_lag
, charttime
, MechVent
, OxygenTherapy
, Extubated
, SelfExtubated

/* -- if this is a mechanical ventilation event, we calculate the time since the last event*/
, case
/* -- if the current observation indicates mechanical ventilation is present*/
/* -- calculate the time since the last vent event*/
when MechVent=1 then
CHARTTIME - charttime_lag
else null
end as ventduration

, LAG(Extubated,1)
OVER
(
partition by icustay_id, case when MechVent=1 or Extubated=1 then 1 else 0 end
order by charttime
) as ExtubatedLag

/* -- now we determine if the current mech vent event is a "new", i.e. they've just been intubated*/
, case
/* -- if there is an extubation flag, we mark any subsequent ventilation as a new ventilation event*/
/* --when Extubated = 1 then 0 -- extubation is *not* a new ventilation event, the *subsequent* row is*/
when
LAG(Extubated,1)
OVER
(
partition by icustay_id, case when MechVent=1 or Extubated=1 then 1 else 0 end
order by charttime
)
= 1 then 1
/* -- if patient has initiated oxygen therapy, and is not currently vented, start a newvent*/
when MechVent = 0 and OxygenTherapy = 1 then 1
/* -- if there is less than 8 hours between vent settings, we do not treat this as a new ventilation event*/
when (CHARTTIME - charttime_lag) > 8
then 1
else 0
end as newvent
/* -- use the staging table with only vent settings from chart events*/
FROM vd0 ventsettings
;

[kiranv_ · Accepted solution]

This might not be straight forward answer but a reference to solve the problem. In your query you are using olap functions for example

 LAG(Extubated,1)
OVER
(
partition by icustay_id, case when MechVent=1 or Extubated=1 then 1 else 0 end
order by charttime
) as ExtubatedLag

below is the paper which tell how to emulate olap functions in SAS. Even though this paper for Teradata, it is very valid for PostgreSQL query you have pasted

http://support.sas.com/resources/papers/proceedings11/019-2011.pdf