Hi! I am trying to run the code below in SAS enterprise guide: proc sql;
create table temp.lab as
select l.gp_patidf, l.gp_exadate as datum, coalesce(l.gp_examne, l.gp_exacod, l.gp_exashb) as code, l.gp_exatxt1,
ROW_NUMBER() OVER(PARTITION BY gp_patidf, gp_examne, gp_exashb ORDER BY gp_exadate desc) AS tel
from sample_lab as l
where exists (
select *
from clean.COVID_infection ep
where l.gp_patidf=ep.gp_patidf
)
and year(gp_exadate)>=2019
and (
gp_exanote like '%covid%'
or gp_examne in ('SCVTBMT','SCVAB','SCVGB','SCVMB','SCATRA','SCASRA','SCVRBK','SCVPRK','SCVRRM','SCVPRM','SCVRRS','SCVPRS','SCVRRA','SCVPRA','ZKTDAQ')
or gp_exacod in ('4002','4005','4004','4003','4033','4012','4029','3999','4030','4000','4031','4001','4032','4011','3234')
or gp_exashb in ('COVID19STA','COVIDBEL')
or gp_epsicpc='R83.03'
);
quit; I then receive the following error: 28 proc sql;
29 create table temp.lab as
30 select l.gp_patidf, l.gp_exadate as datum, coalesce(l.gp_examne, l.gp_exacod, l.gp_exashb) as code, l.gp_exatxt1,
31 ROW_NUMBER() OVER(PARTITION BY gp_patidf, gp_examne, gp_exashb ORDER BY gp_exadate desc) AS tel
____
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.
ERROR 76-322: Syntax error, statement will be ignored. From other topics I have read, I now understand that row_number () and partition by functions are not available in SAS SQL. Some solutions to this, suggested in other topics, I found difficult to incorporate in my code. Does anyone have an alternative? To clarify, I want to know the most recent date ( gp_exadate) and partition by person ( gp_patidf) , gp_examne, gp_exashb. This is needed to further on use in code below (using created variable 'tel'): proc sql;
create table covid_infection_def as
select epi.gp_patidf,
epi.gp_epsicpc,
case when bel.code is not null then 1 else 0 end as quest,
case when epi.datum<j.datum then epi.datum else coalesce(j.datum, epi.datum) end as datum,
v.datum as cordatum,
sta.gp_exatxt1 as Covstatus,
bel.gp_exatxt1 as CovBeleid,
zdag.gp_exatxt1 as ZKTDAQ,
case when epi.hosp=1 or j.hosp=1 or v.hosp=1 then 1 else 0 end as hosp,
case when epi.ic=1 or j.ic=1 or v.ic=1 then 1 else 0 end as ic,
epi.overleden,
epi.gp_epsnote as icpcprob
from clean.COVID_infection epi
left join temp.jour j
on epi.gp_patidf=j.gp_patidf
left join lab sta
on epi.gp_patidf=sta.gp_patidf and sta.code='COVID19STA' and sta.tel=1 /*laatste resultaat per meting per patient*/
left join lab bel
on epi.gp_patidf=bel.gp_patidf and bel.code='COVIDBEL' and bel.tel=1
left join lab zdag
on epi.gp_patidf=zdag.gp_patidf and (zdag.code='ZKTDAQ' or zdag.code='3234') and zdag.tel=1
left join temp.verw v
on epi.gp_patidf=v.gp_patidf;
quit;
... View more