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 (January 2009, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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
Comments: To: sas-l@uga.edu

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;


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