BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Zula
Obsidian | Level 7

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
;

1 ACCEPTED SOLUTION

Accepted Solutions
kiranv_
Rhodochrosite | Level 12

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

1 REPLY 1
kiranv_
Rhodochrosite | Level 12

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

 

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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