Date: Fri, 23 Jan 2009 13:57:12 -0500
Reply-To: Ahmed Al-Attar <ahmed.al-attar@SAS.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Ahmed Al-Attar <ahmed.al-attar@SAS.COM>
Organization: SAS Inc.
Subject: Re: Help on SQL
Note : I posted this to SAS-L already.
Hi,
Here is an alternative approach to what you need, using Proc Summary, which
is threaded, and you avoid sorting the data ;-)
/* The procedure will only output records that, has 'C' greater than 0
(zero) and not matching occurances count */
proc summary data=have nway missing;
class a b;
var c; /* You'll need to change your original data set (have) to save
the 'c' column as a numeric */
output out=work._have_sum(drop=_type_ where=(c GT 0 and C NE _freq_))
sum=;
run;
Ahmed
"SUBSCRIBE SAS-L Dan" <deniseyu001@GMAIL.COM> wrote in message
news:200901231758.n0NBlFJU004005@mailgw.cc.uga.edu...
> Hi. SasLers:
>
> Sorry guys, I have submitted this post after another pose. I am reposting
> it again.
>
> I have a code to do the sorting and pick up records if in a certain
> categories there are not all "0" or "1". I use data step. I believe there
> is a SQL solution. Could some body show me? following question is: is SQL
> solution quicker?
>
> Thanks. The whole code is following.
>
> Dan
>
> data have ;
> length A $10 ;
> input A $ B $ C $ ;
> datalines ;
>
> 014-001-1 1 1
> 014-001-1 1 1
> 014-001-1 1 0
> 014-001-1 1 1
> 014-001-1 1 1
> 014-001-1 1 1
> 014-001-1 1 1
> 014-001-1 2 0
> 014-001-1 2 0
> 014-001-1 2 0
> 014-001-1 2 0
> 014-001-1 2 0
> 014-001-1 2 0
> 014-001-1 2 0
> 014-001-1 2 0
> 014-001-1 2 0
> 014-001-1 2 0
> 014-001-1 2 0
> 014-001-1 2 0
> 014-001-1 2 0
> 014-001-1 2 0
> 014-001-1 2 0
> 014-001-1 2 0
> 014-001-1 2 0
> 014-001-1 2 0
> 014-001-1 3 0
> 014-001-1 3 0
> 014-001-1 3 0
> 014-001-1 3 0
> 014-001-1 3 0
> 014-001-1 3 0
> 014-001-1 3 0
> 014-001-1 3 0
> 014-001-1 3 0
> 014-001-1 3 0
> 014-001-1 3 0
> 016-001-2 1 1
> 016-001-2 1 1
> 016-001-2 1 1
> 016-001-2 1 1
> 016-001-2 1 1
> 016-001-2 1 1
> 016-001-2 1 1
> 016-001-2 2 0
> 016-001-2 2 0
> 016-001-2 2 0
> 016-001-2 2 0
> 016-001-2 2 0
> 016-001-2 2 0
> 016-001-2 2 0
> 016-001-2 2 0
> 016-001-2 2 1
> 016-001-2 2 0
> 016-001-2 2 0
> 016-001-2 2 0
> 016-001-2 2 0
> 016-001-2 2 0
> 016-001-2 2 0
> 016-001-2 2 0
> 016-001-2 2 0
> 016-001-2 2 0
> 016-001-2 3 0
> 016-001-2 3 0
> 016-001-2 3 0
> 016-001-2 3 0
> 016-001-2 3 1
> 016-001-2 3 0
> 016-001-2 3 0
> 016-001-2 3 0
> 016-001-2 3 0
> 016-001-2 3 0
> 016-001-2 3 0
> 016-002-2 1 1
> 016-002-2 1 1
> 016-002-2 1 1
> 016-002-2 1 1
> 016-002-2 1 1
> 016-002-2 1 1
> 016-002-2 1 1
> 016-002-2 2 0
> 016-002-2 2 0
> 016-002-2 2 0
> 016-002-2 2 0
> 016-002-2 2 0
> 016-002-2 2 0
> 016-002-2 2 0
> 016-002-2 2 0
> 016-002-2 2 0
> 016-002-2 2 0
> 016-002-2 2 0
> 016-002-2 2 0
> 016-002-2 2 0
> 016-002-2 2 0
> 016-002-2 2 0
> 016-002-2 2 0
> 016-002-2 2 0
> 016-002-2 2 0
> 016-002-2 3 0
> 016-002-2 3 0
> 016-002-2 3 0
> 016-002-2 3 0
> 016-002-2 3 0
> 016-002-2 3 0
> 016-002-2 3 0
> 016-002-2 3 0
> 016-002-2 3 0
> 016-002-2 3 0
> 016-002-2 3 0
> 016-002-2 4 1
> 016-002-2 4 1
> 016-002-2 4 1
> 016-002-2 4 1
> 016-002-2 4 1
> 016-002-2 4 1
> 016-002-2 4 1
> 016-002-2 5 0
> 016-002-2 5 0
> 016-002-2 5 0
> 016-002-2 5 0
> 016-002-2 5 0
> 016-002-2 5 0
> 016-002-2 5 0
> 016-002-2 5 0
> 016-002-2 5 0
> 016-002-2 5 0
> 016-002-2 5 0
> 016-002-2 5 0
> 016-002-2 5 0
> 016-002-2 5 0
> 016-002-2 5 0
> 016-002-2 5 0
> 016-002-2 5 0
> 016-002-2 5 0
> 016-002-2 6 0
> 016-002-2 6 0
> 016-002-2 6 0
> 016-002-2 6 0
> 016-002-2 6 0
> 016-002-2 6 0
> 016-002-2 6 0
> 016-002-2 6 0
> ;
> run;
>
> proc print ;run;
>
> proc sort data=have ; by a b ; run;
>
> data want ;
> set have ;
> by a b ;
> first=first.b ;
> last=last.b ;
> if b in (1,4) then do ;
> if first.b then do ;
> count=0 ;
> condition=0 ;
> end ;
> count+1 ;
> if c='1' then condition+1 ;
> if last.b and count ne condition then flag=1 ;
> end ;
> if b in (2,3,5,6) then do ;
> if first.b then do ;
> count=0 ;
> condition=0 ;
> end ;
> count+1 ;
> if c='0' then condition+1 ;
> if last.b and count ne condition then flag=1 ;
> end ;
> if last.b and flag ;
> run;
>
> proc print; run;
| 
