Date:         Sat, 22 Oct 2005 04:41:15 +0300
Reply-To:     Nina Harris <sas@MAILINATOR.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Nina Harris <sas@MAILINATOR.COM>
Subject:      help with a sql solution
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset="iso-8859-1"

could this be solved with a proc sql solution?
the goal here is to prioritize the values based on "flag1" and "flag2" but processing
in a data step is really not an option as I have hundreds of millions of rows in a
table

these 12 become 7 selections

data a;input n6 $6. switch $1. flag1 1. flag2 1.;
cards;
000011A10
000011A11
120011A10
120011A11
120011A11
000013B00
000016B00
000019B10
000018B00
000018B01
333019B00
333019B01
;run;
proc sort;by n6 flag1 descending flag2;
data b;set;by n6;
if first.n6 then do;match=0;end;
if flag1=1 and flag2=1 and match=0 then match=1;
if flag1=0 and flag2=1 and match=0 then match=1;
if flag1=1 and flag2=0 and match=0 then match=1;
if flag1=0 and flag2=0 and match=0 then match=1;
if match=1 then output;
data _null_;set ;put(_all_)(=);run;