BookmarkSubscribeRSS Feed
sofia3
Calcite | Level 5

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_IDPurchased_FruitGroupCountCount_Shift

1

ApplesFemale10
1ApplesFemale21
1ApplesFemale32
2ApplesFemale10
2ApplesFemale21
2BananasFemale02
2BananasFemale00
2ApplesFemale10
2ApplesFemale21
2ApplesFemale32
3ApplesMale00
4PearsFemale00
4ApplesFemale10
4OrangesFemale01

 

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;

 

5 REPLIES 5
Tom
Super User Tom
Super User

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
Opal | Level 21

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

Patrick_0-1703126098020.png

 

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

sofia3
Calcite | Level 5

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
Super User
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
Opal | Level 21

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

Patrick_0-1703209107129.png

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1599 views
  • 2 likes
  • 4 in conversation