topic Re: Shift Counter Down by 1 in SAS Programming

Shift Counter Down by 1

sofia3 — Thu, 21 Dec 2023 01:07:08 GMT

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;

Re: Shift Counter Down by 1

Tom — Thu, 21 Dec 2023 01:33:20 GMT

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?

Re: Shift Counter Down by 1

Patrick — Thu, 21 Dec 2023 02:38:01 GMT

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?

Re: Shift Counter Down by 1

sofia3 — Thu, 21 Dec 2023 02:59:33 GMT

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.

Re: Shift Counter Down by 1

Kurt_Bremser — Thu, 21 Dec 2023 09:15:21 GMT

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.

Re: Shift Counter Down by 1

Patrick — Fri, 22 Dec 2023 01:39:50 GMT

@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;