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
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
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;
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...
Look at Jag's code and the lag function usage.
to_prod = prd
from_prd = lag(prd)
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
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
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;
Thanks for help Reeza. It worked...
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: