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