Macro or data step to retrieve a switch date, Please Help..Thank you

[Kanyange]

Hi All,

I have aroound 10,000 customers who switched from one product_id to another. I would like to identify when is the Yearmonth when the Target_field changes from 1 to 0. I am interested to see what was the yearmonth they went from 1 to 0. But not from 0 to 1. Below is an example of how my current data looks like per customer. This customers started with Target_field=1 and he switched to 0 in Year=201406.

Your Help would be much appreciated.

Thank You

id     prod_id Year Target_field
19081 2338 201402 1
19081 2338 201403 1
19081 2338 201404 1
19081 2338 201405 1
19081 2486 201406 0
19081 2486 201407 0
19081 2486 201408 0
19081 2486 201409 0
19081 2486 201410 0
19081 2486 201411 0
19081 2486 201412 0
19081 2486 201501 0
19081 2486 201502 0
19081 2486 201503 0
19081 2486 201504 0
19081 2486 201505 0
19081 2486 201506 0
19081 2486 201507 0
19081 2486 201508 0

[LinusH]

Use a data step using BY and first./last. processing with RETAIN.

[PGStats]

Simple BY processing can do this

data switches;
set have; by id target_field notsorted;
if 
    first.target_field and 
    not first.id and 
    target_field=0 then output;
run;