Hi I have the following data below. For each customer I want to take the top four ranks but within these four obs there must only be one observation that has flag_2=1 and flag__1=0. I have highlighted in red the records I would want selected. I'm struggling to work out how I can do this. thanks
Cust | Rank | Flag_1 | Flag_2 |
---|---|---|---|
A | 1 | 1 | 0 |
A | 2 | 1 | 1 |
A | 3 | 1 | 1 |
A | 4 | 0 | 1 |
A | 5 | 0 | 1 |
A | 6 | 1 | 0 |
A | 7 | 0 | 0 |
B | 1 | 1 | 0 |
B | 2 | 1 | 0 |
B | 3 | 0 | 0 |
A solution amongst others:
data have;
input Cust $ Rank Flag_1 Flag_2;
cards;
A 1 1 0
A 2 1 1
A 3 1 1
A 4 0 1
A 5 0 1
A 6 1 0
A 7 0 0
B 1 1 0
B 2 1 0
B 3 0 0
;
run;
proc sort data=have; by cust descending rank; run;
data want(keep=cust rank flag_1 flag_2);
set have;
by cust;
retain zSum zOne;
if first.cust then do; zSum=0; zOne=0; end;
if (zSum <4) and not((flag_1=0) and (flag_2=1)) then do;
zSum+1;
output;
end;
if (zOne=0) and (zSum<4) and ((flag_1=0) and (flag_2=1)) then do;
zSum+1;
zOne+1;
output;
end;
run;
@Syndey , Here you go if you fancy using DO LOOP:
data have; /*Your sample test dataset*/
input Cust $ Rank Flag_1 Flag_2;
cards;
A 1 1 0
A 2 1 1
A 3 1 1
A 4 0 1
A 5 0 1
A 6 1 0
A 7 0 0
B 1 1 0
B 2 1 0
B 3 0 0
;
run;
proc sort data=have;
by cust rank;
run;
data want(drop=n); /*Your Output wanted dataset*/
n=0;
do until(last.cust);
set have;
by cust;
n+1;
end;
do _n_=1 by 1 until(last.cust);
set have;
by cust;
if _n_ in (1,2) or _n_=n-1 or _n_=n then output;
end;
run;
Naveen Srinivasan
L&T Infotech
I don't see one observation that has flag_2=1 and flag__1=0 within the obs marked in red .
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.