Flagging Values Only When Certain Changes Are Met, month over month

[ScottSz]

Hey, I'm trying to figure out whats the best way to identify the last new/remove activity within my data set without flagging other activities:

Example:

Month   ID   Activity          Want_this

Jan        1     New_jan       New_jan

Feb       1      Nothing        New_jan

Mar       1      Nothing        New_jan

Apr        1      remove_apr New_jan

May       1      Nothing       remove_apr

Jun         1     New_Jun    remove_apr

Jul          1     Nothing       New_Jun

Aug        1     remove_aug   New_Jun

Sep        1     Nothing         remove_aug

[Astounding]

Before you think about programming with this data, better fix it.

With MONTH being a three-letter abbreviation, you can never sort your data.  If you do sort it, you can never put it back into its original order.  Better change MONTH to a number from 1 through 12.  While you are at it, add a YEAR variable as well.  You may have only one year in the data now, but that can change in a few months.

You will need to explain what "flagging" means to you.  

You will need to explain the rules.  For example, the January activity seems to apply beginning in January.  But the April activity seems to apply beginning in May.

[ScottSz]

Thanks for relaying your concerns, I have fixed the data set to now look like this:

Date                     ID      Activity             Want_this

31Jan2020            1     New_jan        

29Feb2020           1     Nothing                 New_jan

31Mar2020            1     Nothing                New_jan

30Apr2020            1    remove_apr          New_jan

31May2020           1     Nothing                remove_apr

30Jun2020            1    New_Jun             remove_apr

31Jul2020             1    Nothing                New_Jun

31Aug2020           1    remove_aug         New_Jun

30Sep2020           1    Nothing                remove_aug

So essentially what I would like to do is when either a "new" or a "remove" activity occurs I want the next month to show that last activity.  And if nothing happens continue showing the last activity that happened.  So example Jan first a "new" activity occurs, in Feb I want to show in "WANT_THIS" column the January activity, and nothing will change in this column until May because in Apr a "remove" activity occured.  Let me know if I can clarify more and I really appreciate the help so far.

[mkeintz · Accepted solution]

Here's what I think your intended rules are:

1. If the record-in-hand is the start of an ID, then WANT_THIS is blank.
2. The value of ACTIVITY never influences the current value of WANT_THIS.  It only impacts WANT_THIS for subsequent observations.  In particular,
   1. If the current ACTIVITY is "Nothing", then make no change to the upcoming observation of WANT_THIS.
   2. Otherwise the upcoming WANT_THIS value is set to the current value of ACTIVITY

You haven't provided data in the format of a data step, so here is some untested code:

data want;
  set have;
  by id;
  length want_this $11 ;
  retain want_this;

  if first.id then want_this=' ';
  output;
  if activity^='Nothing' then want_this=activity;
run;

One way to modify the value of WANT_THIS for subsequent observations, but not the current obs, is to output the observation prior to modifying WANT_THIS, which is declared as a retained variable.   The only time you want to modify the value of WANT_THIS for the record-in-hand is when you are starting a new ID group.

[ScottSz]

this worked exactly how I wanted it thank you for your help! I couldnt get anything to work at the beginning..thought I had to use the lag function somehow.