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
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;
Thanks Art ..it works out well..
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.
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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.