For the below code - note the NOTE that tells you it's re-merging summary statistics with data You have to wrap non-group-by variables in summary functions to get anything meaningful - so e.g. max(DT), min(meas1), avg(meas2), ...

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Ben Powell Sent: Wednesday, March 07, 2012 4:06 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: Replicating PROC SORT NODUPKEY in ***

Am I missing something here?

proc sql; create table nodupsql as select site, subject, DT, meas1, meas2, meas3 from apple group by site, subject order by site, subject, dt; quit;

proc compare base=apple compare=nodupsql;run;/* Identical */

Regards

On Tue, 6 Mar 2012 06:15:00 -0800, Brian Wallace <brian_c_wallace@YAHOO.COM> wrote:

>� >I know this is verboten.� These kind of questions "aren't served here" but I'm the only SAS programmer on a team of SQL programmers and think there has to be a (relatively) easy way to do this in SQL.� The SQL programmers claim there isn't.� I know this isn't really a SAS question. >� >I just want one unique record per site/subject pair.� Easily done in SAS. >� >Like this: >� >DATA APPLE; >INPUT SITE $ SUBJECT $ DT $ MEAS1 MEAS2 MEAS3; >CARDS; >001 001 01JAN2012 3.45 5.67 2.14 >001 001 01FEB2012 2.45 3.64 2.14 >001 001 01MAR2012 7.45 5.64 2.14 >001 001 01APR2012 1.45 2.33 2.14 >001 002 01MAY2012 5.45 5.64 2.14 >001 002 01JUN2012 9.45 1.64 2.14 >001 002 01JUL2012 3.45 3.64 2.14 >001 003 01AUG2012 2.45 5.64 2.88 >001 003 01SEP2012 4.45 4.64 2.14 >001 003 01OCT2012 5.45 2.64 2.14 >002 001 01NOV2012 1.32 5.64 2.14 >002 001 01DEC2012 1.45 3.64 2.18 >002 001 01JAN2012 7.45 5.64 2.14 >002 001 01FEB2012 8.45 4.64 2.14 >002 002 01MAR2012 9.45 5.64 2.14 >002 002 01APR2012 2.45 5.64 2.14 >002 002 01MAY2012 3.45 7.34 2.14 >003 001 01JUN2012 5.45 5.64 2.14 >003 001 01JUL2012 4.45 7.64 2.14 >003 002 01AUG2012 3.45 6.64 2.14 >003 002 01SEP2012 2.45 4.64 2.14 >003 002 01OCT2012 5.45 5.64 2.14 >003 003 01NOV2012 6.45 2.64 2.14 >003 003 01DEC2012 3.45 5.64 2.14 >; >PROC SORT DATA = APPLE NODUPKEY; >BY SITE SUBJECT; >RUN; >� >gives me EXACTLY what I want.� One record each: >� >Obs SITE SUBJECT DT MEAS1 MEAS2 MEAS3 >1 001 001 01JAN201 3.45 5.67 2.14 >2 001 002 01MAY201 5.45 5.64 2.14 >3 001 003 01AUG201 2.45 5.64 2.88 >4 002 001 01NOV201 1.32 5.64 2.14 >5 002 002 01MAR201 9.45 5.64 2.14 >6 003 001 01JUN201 5.45 5.64 2.14 >7 003 002 01AUG201 3.45 6.64 2.14 >8 003 003 01NOV201 6.45 2.64 2.14 >� >But can't you do that in SQL?� select DISTINCT would give you unique site/subjects but apparently you can't just put them in parens and have the other fields not be unique. >� >I thought of selecting just the distinct site/subjects�and then left joining the full listing but that doesn't quite work either. >� >I know this isn't a SAS question but whatever the solution to this is, I can tell them that SAS can do the same thing in 9 words.� I can show them how much better SAS is.� :) >� >Any help would be appreciated. >� >Thank you, >� >Brian Wallace >�