SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

PostgreSQL code translate into FEDSQL or SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

PostgreSQL code translate into FEDSQL or SAS

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
;


Accepted Solutions
Solution
‎10-19-2017 09:32 PM
PROC Star
Posts: 503

Re: PostgreSQL code translate into FEDSQL or SAS

[ Edited ]

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

 

View solution in original post


All Replies
Solution
‎10-19-2017 09:32 PM
PROC Star
Posts: 503

Re: PostgreSQL code translate into FEDSQL or SAS

[ Edited ]

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

 

☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 204 views
  • 1 like
  • 2 in conversation