Help using Base SAS procedures

FEDSQL vs SQL Grouping

Reply
New Contributor
Posts: 3

FEDSQL vs SQL Grouping

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;

Super User
Posts: 5,882

Re: FEDSQL vs SQL Grouping

[ Edited ]
Posted in reply to MatthewWiedel

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.

Data never sleeps
New Contributor
Posts: 3

Re: FEDSQL vs SQL Grouping

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;

Ask a Question
Discussion stats
  • 2 replies
  • 261 views
  • 4 likes
  • 2 in conversation