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 .
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.