topic Flagging Values Only When Certain Changes Are Met, month over month in SAS Programming

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

ScottSz — Tue, 15 Dec 2020 16:33:38 GMT

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

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

Astounding — Tue, 15 Dec 2020 17:14:32 GMT

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.

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

ScottSz — Tue, 15 Dec 2020 17:24:50 GMT

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.

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

mkeintz — Tue, 15 Dec 2020 18:27:50 GMT

Here's what I think your intended rules are:

If the record-in-hand is the start of an ID, then WANT_THIS is blank.
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.

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

ScottSz — Tue, 15 Dec 2020 21:11:30 GMT

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.