LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (April 2005, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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


Back to: Top of message | Previous page | Main SAS-L page