data selection

[raveena]

Hi all,

I have a dataset with columns clam,LNno and IND..
If whole CLAM has the ind Y , then the clam is selected for processing..
if whole clam has the ind 'Y' and 'N' then select the REVIND='N' not 'Y'.

CLAM      LNNO        IND
12345    001          Y
12345    002          Y
12345    003          Y

23456    001            Y
23456    002            N

34567    001            Y
34567    002            N

78968    001            Y
78968    002            Y
78968    003            Y

Output should be

CLAM    LINENO    IND
12345    001        Y
12345    002        Y
12345    003        Y

23456    002        N

34567    002        N

78698    001        Y
78698    002        Y
78698    003        Y

please advice me in programming how to get this output..

Thanks in advance

[art297]

You didn't indicate how to treat an all N combination, so you may have to change the following a bit, but it should get you started:

data have;

  input (CLAM      LNNO        IND) ($);

  cards;

12345    001          Y

12345    002          Y

12345    003          Y

23456    001            Y

23456    002            N

34567    001            Y

34567    002            N

78968    001            Y

78968    002            Y

78968    003            Y

;

proc sql noprint;

  create table need as

    select distinct CLAM, min(IND) as minIND,

           max(IND) as maxIND

      from have

        group by CLAM

;

quit;

data want (drop=min: max:;

  merge have need;

  by CLAM;

  if minIND eq maxIND eq "Y" then do;

    IND="Y";

    output;

  end;

  else if first.CLAM then do;

    IND="N";

    output;

  end;

run;

[raveena]

Thanks Art ..it works out well..

[raveena]

Art,

In case if we have multiple N values for clamno,

data have;

  input (CLAM      LNNO        IND) ($);

  cards;

12345    001           Y

12345    002           Y

12345    003           Y

23456    001            Y

23456    002            N

34567    001            Y

34567    002            N

34567    003            N

78968    001            Y

78968    002            Y

78968    003            Y

;

Please let me know.

[raveena]

I got the desired output..i tried the code like this,

data want (drop=min: max:);

merge have need;

by CLAM;

if minIND eq maxIND eq "Y" then do;

IND="Y";

output;

end;

else if minIND eq "N" then do;

if IND eq "N";

output;

end;

run;

Thanks,

Sudha

[MikeZdeb]

Hi ... here's another idea.  Just sort by CLAM and IND and then check if the

first IND within a CLAM is an N or a Y.  If it's an N, keeps the Ns.  If it's a Y, keep the Ys.

data have;

input CLAM  LNNO : $3. IND : $1.;

datalines;

12345    001           Y

12345    002           Y

12345    003           Y

23456    001           Y

23456    002           N

34567    001           Y

34567    002           N

34567    003           N

78968    001           Y

78968    002           Y

78968    003           Y

;

run;

proc sort data=have;

by clam ind;

run;

data want;

do until (last.clam);

   set have;

   by clam;

   if first.clam and ind eq 'N' then hold = 1;

   if ind eq 'Y' and missing(hold) or

      ind eq 'N' and ^missing(hold) then output;

end;

drop hold;

run;

proc print data=want noobs;

run;

CLAM    LNNO    IND

12345    001      Y

12345    002      Y

12345    003      Y

23456    002      N

34567    002      N

34567    003      N

78968    001      Y

78968    002      Y

78968    003      Y

[Ksharp]

data have;
  input (CLAM      LNNO        IND) ($);
  cards;
12345    001          Y
12345    002          Y
12345    003          Y
23456    001            Y
23456    002            N
34567    001            Y
34567    002            N
78968    001            Y
78968    002            Y
78968    003            Y
; 
run;
proc sql noprint;
 create table want as
  select *
   from have
    group by clam
     having count(distinct ind) eq 1 and ind='Y'

 union all corresponding
   
  select *
   from have
    group by clam
     having count(distinct ind) eq 2 and ind='N';
quit;

Ksharp