BookmarkSubscribeRSS Feed
pp2014
Fluorite | Level 6

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

8 REPLIES 8
Jagadishkatam
Amethyst | Level 16

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

Thanks,
Jag
Reeza
Super User

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
Fluorite | Level 6

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
Super User

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

to_prod = prd

from_prd = lag(prd)

pp2014
Fluorite | Level 6

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

Thanks for help Reeza.  It worked...

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 891 views
  • 1 like
  • 3 in conversation