DATA Step, Macro, Functions and more

Do loops not sure if I am using the right step

Reply
Occasional Contributor
Posts: 5

Do loops not sure if I am using the right step


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

CustRankFlag_1Flag_2
A110
A211
A311
A401
A501
A610
A700
B110
B210
B300
Contributor
Posts: 52

Re: Do loops not sure if I am using the right step


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;

Frequent Contributor
Posts: 115

Re: Do loops not sure if I am using the right step

@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

Super User
Posts: 10,035

Re: Do loops not sure if I am using the right step

I don't see one observation that has flag_2=1 and flag__1=0  within the obs marked in red .

Ask a Question
Discussion stats
  • 3 replies
  • 254 views
  • 1 like
  • 4 in conversation