Date:         Wed, 7 Mar 2012 04:06:25 -0500
Reply-To:     Ben Powell <bpowell555@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ben Powell <bpowell555@GMAIL.COM>
Subject:      Re: Replicating PROC SORT NODUPKEY in ***
Comments: To: Brian Wallace <brian_c_wallace@YAHOO.COM>
Content-Type: text/plain; charset=ISO-8859-1

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
>�