Shift Counter Down by 1

[sofia3]

I am having an issue with shifting a counter Column down in SAS by One as it wont meet my conditions.

data Distinct_Member1;

set Distinct_Member;
by Member_ID startyear;
retain Count 0;
if first.Member_ID then Count = 0;
if (Purchased_Fruit in ('Apples') and Quantity_Purchased not in ('None') and group not in
('Females')) then Count = Count + 1;
if (Purchased_Fruit not in ('Apples') OR Quantity_Purchased in ('None') OR group in
('Females')) then Count = Count + 1
run;

This is the code for the original Counter but now I want to Shift the Counter by 1 down as below

Member_ID	Purchased_Fruit	Group	Count	Count_Shift
1	Apples	Female	1	0
1	Apples	Female	2	1
1	Apples	Female	3	2
2	Apples	Female	1	0
2	Apples	Female	2	1
2	Bananas	Female	0	2
2	Bananas	Female	0	0
2	Apples	Female	1	0
2	Apples	Female	2	1
2	Apples	Female	3	2
3	Apples	Male	0	0
4	Pears	Female	0	0
4	Apples	Female	1	0
4	Oranges	Female	0	1

I have tried a combination of this (below) and lag functions however I never seem to have the desired result. Any help or direction with how to achieve the Count_Shift column is appreciated! Thank you.

data Distinct_Member2;
set Distinct_Member1;
by Member_ID;

retain Count_Shift 0; if first.Member_ID then Count_Shift = 0;
else if Count >0 then Count_Shift = Count - 1;
run;

[Tom]

I don't understand what "shift counter down by 1" means.

Do you mean subtract one from a variable?

data want;
  set have;
  shift_counter=counter-1;
run;

Do you mean making a new count only starting from 0 instead of one?  Why not just change the initial value?

[Patrick]

Why is count_shift in below highlighted example Zero and not Three?

Does shift just mean "count value from previous row?" or some other logic?

[sofia3]

The reason is that it is not 3 is because it is a new Member_ID, and as such it restarts. I am attempting to get shift counter as it would allow me to calculate probability and expectancy for this group of people. 

[Kurt_Bremser]

data want;
set Distinct_Member;
by Member_ID startyear;
retain count count_shift;
if first.Member_ID
then do;
  count = 0;
  count_shift = 0;
end;
count + (Purchased_Fruit in ('Apples') and Quantity_Purchased not in ('None') and group not in
('Females'));
count +  (Purchased_Fruit not in ('Apples') or Quantity_Purchased in ('None') or group in
('Females'));
output;
count_shift = count;
run;

The explicit OUTPUT statement prevents the implicit OUTPUT at the end of a DATA step iteration.

[Patrick]

@sofia3 Is the logic you're asking for as simple as below?

data have;
  infile datalines dsd truncover;
  input Member_ID Purchased_Fruit $ Group $ Count Count_Shift;
  datalines;
1,Apples,Female,1,0
1,Apples,Female,2,1
1,Apples,Female,3,2
2,Apples,Female,1,0
2,Apples,Female,2,1
2,Bananas,Female,0,2
2,Bananas,Female,0,0
2,Apples,Female,1,0
2,Apples,Female,2,1
2,Apples,Female,3,2
3,Apples,Male,0,0
4,Pears,Female,0,0
4,Apples,Female,1,0
4,Oranges,Female,0,1
;

data want;
  set have;
  by Member_ID;
  Count_Shift_2= lag(count);
  if first.member_id then Count_Shift_2=0;
  comp_flg= count_shift_2=count_shift;
run;

proc print data=want;
run;