I have this code in SAS, I'm trying to write SQL equivalent. I have no experience in SAS.

data Fulls Fulls_Dupes;
    set Fulls;
    by name, coeff, week;
    if rid = 0 and ^last.week then output Fulls_Dupes;
    else output Fulls;
run;

I tried the following, but didn't produce the same output:

Select * from Fulls where rid = 0 groupby name,coeff,week

is my sql query correct ?

CodePudding user response：

SQL does not have a concept of observation order. So there is no direct equivalent of the LAST. concept. If you have some variable that is monotonically increasing within the groups defined by distinct values of name, coeff, and week then you could select the observation that has the maximum value of that variable to find the observation that is the LAST.

So for example if you also had a variable named DAY that uniquely identified and ordered the observations in the same way as they exist in the FULLES dataset now then you could use the test DAY=MAX(DAY) to find the last observation. In PROC SQL you can use that test directly because SAS will automatically remerge the aggregate value back onto all of the detailed observations. In other SQL implementations you might need to add an extra query to get the max.

 create table new_FULLES as
   select * from FULLES
   group by name, coeff, week
   having day=max(day) or rid ne 0
 ;

SQL also does not have any concept of writing two datasets at once. But for this example since the two generated datasets are distinct and include all of the original observations you could generate the second from the first using EXCEPT.

So if you could build the new FULLS you could get FULLS_DUPES from the new FULLS and the old FULLS.

create table FULLS_DUPES as
  select * from FULLES
  except
  select * from new_FULLES
;