Help with adding flag for Product Switch

[pp2014]

I have the data as follows:   I want the flag when the products switches from SP to some other product (Fr_SP) or from some other product to SP (To_SP)

Data Have;

input Cust_id prd eff_dt anydtdte10.;

format eff_dt date9.;

cards;

1 SP 20140721

1 GL 20140822

1 GL 20140903

1 GL 20141001

1 SP 20141021

1 SP 20141105

1 SP 20141208

1 TA 20150103

1 TA 20150209

I want the output as follows:

Cust_id   prd    eff_dt      Flag

1          SP   20140721

1          GL   20140822    Fr_SP

1          GL   20140903

1          GL   20141001

1          SP   20141021   To_SP

1          SP   20141105

1          SP   20141208

1          TA   20150103   Fr_SP

1          TA   20150209

Thanks

[Jagadishkatam]

Hi,

Please try the below code

Data Have(drop=new);

input Cust_id prd$ eff_dt anydtdte10.;

new=lag(prd);

if _n_ ne 1 then do;

if prd ne new and new='SP' then flag='Fr_SP';

else if prd ne new and new^='SP' then flag='To_SP';

end;

format eff_dt date9.;

cards;

1 SP 20140721

1 GL 20140822

1 GL 20140903

1 GL 20141001

1 SP 20141021

1 SP 20141105

1 SP 20141208

1 TA 20150103

1 TA 20150209

;

Thanks,

Jag

[Reeza]

This will account for multiple customer ID's as well.

Neither of these solutions account for a transition from say, GL to TA and then back to GL.

If that's a possibility it's probably easier with Jag's solution using the lag if that is the case.

data want;

set have;

by cust_id prd notsorted;

if not first.cust_id and first.prd then do;

  if prd='SP' then flag='TO_SP';

  else if prd^='SP' then flag='FR_SP';

end;

run;

[pp2014]

Thanks Jag and Reeza.

I wanted  output to show switches from_Prod and To_Prod as follows for the following data:

Data Have;

input Cust_id prd eff_dt anydtdte10.;

format eff_dt date9.;

cards;

1 SP 20140721

1 GL 20140822

1 GL 20140903

1 GL 20141001

1 SP 20141021

1 SP 20141105

1 SP 20141208

1 TA 20150103

1 TA 20150209

2 SP 20150103

2 GL 20150209

Output should be as follows:

Cust_id   prd               eff_dt         From_Prod          To_Prod

1            SP            20140721

1            GL            20140822        SP                      GL

1            GL            20140903        GL                      GL

1            GL            20141001        GL                      GL

1            SP            20141021        GL                      SP

1            SP            20141105        SP                      SP

1            SP            20141208        SP                      SP

1            TA            20150103        SP                       TA

1            TA            20150209        TA                       TA

2            SP            20150110     

2            GL            20150215         SP                      GL

Thanks...

[Reeza]

Look at Jag's code and the lag function usage.

to_prod = prd

from_prd = lag(prd)

[pp2014]

Hi Reeza, I did run the Jag's code as below but output I am getting is not  correct:

Data Have;

input Cust_id prd$ eff_dt anydtdte10.;

format eff_dt date9.;

cards;

1 SP 20140721

1 GL 20140822

1 GL 20140903

1 GL 20141001

1 SP 20141021

1 SP 20141105

1 SP 20141208

1 TA 20150103

1 TA 20150209

2 SP 20150110

2 TA 20150215

data want;

set have;

to_prd=lag(prd);

from_prd = lag(prd);

run;

proc print data = want noobs;

var cust_id eff_dt  from_prd  to_prd;

run;

Output I am getting is as follows which is wrong:

                                              Cust_id       eff_dt    from_prd    to_prd

                                                1       21JUL2014

                                                1       22AUG2014       SP         SP

                                                1       03SEP2014       GL         GL

                                                1       01OCT2014       GL         GL

                                                1       21OCT2014       GL         GL

                                                1       05NOV2014       SP         SP

                                                1       08DEC2014       SP         SP

                                                1       03JAN2015       SP         SP

                                                1       09FEB2015       TA         TA

                                                2       10JAN2015       TA         TA

                                                2       15FEB2015       SP         SP

[pp2014]

Sorry below is the corrected code. But still not getting correct output..

Data Have;

input Cust_id prd$ eff_dt anydtdte10.;

format eff_dt date9.;

cards;

1 SP 20140721

1 GL 20140822

1 GL 20140903

1 GL 20141001

1 SP 20141021

1 SP 20141105

1 SP 20141208

1 TA 20150103

1 TA 20150209

2 SP 20150110

2 TA 20150215

data want;

set have;

to_prd=  prd;

from_prd = lag(prd);

run;

proc print data = want noobs;

var cust_id eff_dt  from_prd  to_prd;

run;

Output I am getting is as follows which is wrong:

                                            Cust_id       eff_dt    from_prd    to_prd

                                                1       21JUL2014                    SP

                                                1       22AUG2014       SP         GL

                                                1       03SEP2014       GL         GL

                                                1       01OCT2014       GL         GL

                                                1       21OCT2014       GL         SP

                                                1       05NOV2014       SP         SP

                                                1       08DEC2014       SP         SP

                                                1       03JAN2015       SP         TA

                                                1       09FEB2015       TA         TA

                                                2       10JAN2015       TA         SP

                                                2       15FEB2015       SP         TA

[Reeza]

Use first. logic to set it to missing if it's the first for the Customer ID.

data want;

set have;

by cust_id;

to_prd=  prd;

from_prd = lag(prd);

if first.cust_id then call missing(to_prd, from_prd);

run;

[pp2014]

Thanks for help Reeza.  It worked...