BookmarkSubscribeRSS Feed
raveena
Obsidian | Level 7

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

6 REPLIES 6
art297
Opal | Level 21

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
Obsidian | Level 7

Thanks Art ..it works out well..

raveena
Obsidian | Level 7

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
Obsidian | Level 7

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
Rhodochrosite | Level 12

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
Super User
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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 860 views
  • 0 likes
  • 4 in conversation