To find out the dates on which status changes

[kashish_joker]

Hello All,

Here i am having a data which contain a field status which varies with respect to date, what I want is find the dates on which its status get OFF and after getting OFF on which date if start working.

data have;
input key System_Date $ status $;
datalines;
1 1-08-18 ON
1 2-08-18 OFF
1 3-08-18 OFF
1 4-08-18 OFF
1 5-08-18 OFF
2 1-08-18 ON
2 2-08-18 OFF
2 3-08-18 OFF
2 4-08-18 OFF
2 5-08-18 ON
;
run;

this is how I want the output

key System_Date $ status $ red_date $ green_date $
1   1-08-18             ON
1   2-08-18            OFF  2-08-18
1   3-08-18            OFF  2-08-18
1   4-08-18            OFF  2-08-18
1   5-08-18            OFF   2-08-18
2   1-08-18            ON
2   2-08-18           OFF  2-08-18
2   3-08-18           OFF  2-08-18
2   4-08-18           OFF  2-08-18
2   5-08-18           ON    2-08-18             5-08-18

Thanks in Advance.

[RW9 · Accepted solution]

Use retain and if statements (going in meeting so will be breif):

data want;
  set have;
  length red_date green_date $20;
  retain red_date green_date;
  by key;
  if first.key then call missing(red_date,green_date);
  if status="OFF" and red_date="" then red_date=system_date;
  if status="ON" and red_date ne "" then green_date=system_date;
run;

Edit by KB: Added a "n" to "the" in the "if first." statement.

[kashish_joker]

Thanks for your quick response.

But need more help on this,

I am attaching the output that I an getting with your code(Namiing: HAVE).

Their ia a issue in that the red_date is not updating, it is showing the first date but I want red_date must be latest date on which status goes "OFF" 

for you help also attaching the output needed(Namming: WANT).

[RW9]

Sorry, I am not following the logic of your want.  Why does red-date go back to 1-08-18 at 04-8-18?

Should just be a matter of changing the if statements yo what you want.

[Kurt_Bremser]

---

@kashish_joker wrote:

Hello All,

Here i am having a data which contain a field status which varies with respect to date, what I want is find the dates on which its status get OFF and after getting OFF on which date if start working.

data have;
input key System_Date $ status $;
datalines;
1 1-08-18 ON
1 2-08-18 OFF
1 3-08-18 OFF
1 4-08-18 OFF
1 5-08-18 OFF
2 1-08-18 ON
2 2-08-18 OFF
2 3-08-18 OFF
2 4-08-18 OFF
2 5-08-18 ON
;
run;

this is how I want the output

key System_Date $ status $ red_date $ green_date $
1   1-08-18             ON
1   2-08-18            OFF  2-08-18
1   3-08-18            OFF  2-08-18
1   4-08-18            OFF  2-08-18
1   5-08-18            OFF   2-08-18
2   1-08-18            ON
2   2-08-18           OFF  2-08-18
2   3-08-18           OFF  2-08-18
2   4-08-18           OFF  2-08-18
2   5-08-18           ON    2-08-18             5-08-18

Thanks in Advance.

---

Kudos for posting a very well composed question (example data, clear rule, expected output). Given the many other questions we see here that need a ping-pong of re-requests, this is exceptional for a first-time poster.

There's room for only one little improvement: use the {i} or little running man icons for code (see my third footnote).

[Astounding]

Just a note of caution about your data ...

Storing your SYSTEM_DATE as a character string will make life difficult down the road.  Unless your data ranges over a very limited set of dates, you won't be able to sort the data and put the observations in chronological order.  The problem occurs if the data ever crosses over more than one month.  SAS handles this by storing dates as numeric values, but you will need to study up on what SAS expects and how to get your data into that form.  If you must use character strings, use YMD order and the full set of digits possible, such as:

2018-08-01 (assuming that represents August 1, 2018)