Date:         Thu, 28 Apr 2005 11:50:47 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: How to do if first.var1 in proc sql?
Comments: To: Rusty Shackleford <rs@NATCHIE.MINE.NU>
Content-Type: text/plain; charset="us-ascii"

Rusty:
Unless I am missing something, your program will select the least common
combination of v1 with a v2 value.

In SQL, the HAVING clause sets constraints on summary values. Try ....
    proc sql;

        create table d1 as
        select distinct v1, v2, count(*) as pop
        from d
        group by v1 having pop=max(pop)
        order by v1

    quit;
Sig
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Rusty Shackleford
Sent: Thursday, April 28, 2005 11:18 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: How to do if first.var1 in proc sql?


I have a table two vars, v1 and v2. One v1 value can have multiple v2
values.  I want to reduce this table down to one row per v1 and I want
to keep the most common v2 var associated with that v1 value.

I do the first part in proc sql, but I'm forced to resort to a data step
for the second half.  Can anyone help?

    proc sql;

        create table d1 as
        select distinct v1, v2, count(*) as pop
        from d
        group by v1, v2
        order by v1, pop;

    quit;

    data d2;
        set d1;
        by v1;
        if first.v1;
    run;

How to do this data step in proc sql?

TIA