topic FEDSQL vs SQL Grouping in SAS Procedures

FEDSQL vs SQL Grouping

MatthewWiedel — Fri, 23 Feb 2018 15:25:14 GMT

SQL works as intended

proc sql;
create table ds as select * from sdtm.ds
where dscat='DISP EVENT'
group by usubjid
having visitnum=max(visitnum)
order by usubjid, visitnum;
run;
quit;

FEDSQL gives ERROR: Column "DS.STUDYID" must be GROUPed or used in an aggregate function

It seems as if the FEDSQL requires all the selected variables to be grouped, but then it doesn't take the latest visit.

Is there a way to replicate the SQL code with FEDSQL?

proc fedsql;
create table ds3 as select * from sdtm.ds
where dscat='DISP EVENT'
group by usubjid
having visitnum=max(visitnum)
order by usubjid, visitnum;
run;
quit;

Re: FEDSQL vs SQL Grouping

LinusH — Mon, 26 Feb 2018 12:58:36 GMT

This remerging with original data that PROC SQL does, is not ANSI standard. Which it seems that FedSQL is following at least in this case.
In later implementations of SQL this kind of functionality is handled in window functions. They, however to my knowledge, is not implemented (yet) in FedSQL.
So the alternative would be to do the grouping in an in-line view and join with the original data.

Re: FEDSQL vs SQL Grouping

MatthewWiedel — Fri, 23 Feb 2018 16:26:18 GMT

Thank you for the information; I used your suggestion and looked up an ANSI solution. Here it is:

proc fedsql;
create table ds3 as select a.*
from sdtm.ds a,
(select max(visitnum) as maxvis, usubjid from sdtm.ds group by usubjid) b
where a.usubjid=b.usubjid and
a.visitnum=b.maxvis and
a.dscat='DISP EVENT'
;
run;
quit;