topic data selection in SAS Procedures

data selection

raveena — Thu, 04 Aug 2011 18:33:56 GMT

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

data selection

art297 — Thu, 04 Aug 2011 19:23:30 GMT

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;

data selection

raveena — Thu, 04 Aug 2011 19:30:57 GMT

Thanks Art ..it works out well..

data selection

raveena — Thu, 04 Aug 2011 19:53:15 GMT

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.

data selection

raveena — Thu, 04 Aug 2011 20:01:50 GMT

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

Re: data selection

MikeZdeb — Fri, 05 Aug 2011 03:04:53 GMT

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

Re: data selection

Ksharp — Tue, 09 Aug 2011 09:52:48 GMT

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