BookmarkSubscribeRSS Feed
MatthewWiedel
Calcite | Level 5

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;

2 REPLIES 2
LinusH
Tourmaline | Level 20

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
MatthewWiedel
Calcite | Level 5

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;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1544 views
  • 4 likes
  • 2 in conversation