topic Re: Help with adding flag for Product Switch in SAS Procedures

Help with adding flag for Product Switch

pp2014 — Tue, 03 Mar 2015 02:02:22 GMT

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

Re: Help with adding flag for Product Switch

Jagadishkatam — Tue, 03 Mar 2015 02:18:01 GMT

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

Re: Help with adding flag for Product Switch

Reeza — Tue, 03 Mar 2015 02:59:21 GMT

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;

Re: Help with adding flag for Product Switch

pp2014 — Tue, 03 Mar 2015 17:19:38 GMT

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...

Re: Help with adding flag for Product Switch

Reeza — Tue, 03 Mar 2015 17:27:00 GMT

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

to_prod = prd

from_prd = lag(prd)

Re: Help with adding flag for Product Switch

pp2014 — Tue, 03 Mar 2015 17:42:37 GMT

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

Re: Help with adding flag for Product Switch

pp2014 — Tue, 03 Mar 2015 17:54:46 GMT

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

Re: Help with adding flag for Product Switch

Reeza — Tue, 03 Mar 2015 18:01:38 GMT

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;

Re: Help with adding flag for Product Switch

pp2014 — Tue, 03 Mar 2015 18:09:34 GMT

Thanks for help Reeza. It worked...