Lag Function

Reply
Occasional Contributor
Posts: 12

Lag Function

 

My data looks like below 

 

Cust_id_lag and policy_lag are the columns on which i applied lag for the first two columns of the below dataset.


Ploicy_NO Cust_iD Smo_Flag Cust_ID_lag Policy_lag
1011 36543 Y . .
1011 36543 N 36542 1011
1011 45654 N 36543 1011
2011 77567 Y 45654 1011
2011 77567 N 77567 2011

 

My output should be 

 

I need a new flag accordingly as New_smo_flag 

My conditon should be if policy_no=Policy_lag   and Cust_iD=Cust_ID_lag   then New_Smo_Flag=lag(Smo_Flag);


Ploicy_NO Cust_iD Smo_Flag   Cust_ID_lag  Policy_lag   New_Smo_Flag
1011 36543 Y     .         .     Y
1011 36543 N 36542 1011 Y
1011 45654 N 36543 1011 N
2011 77567 Y 45654 1011 Y
2011 77567 N 77567 2011 Y

 

Please need the code for this urgently. asap.

Drop comments if need more information

Super User
Posts: 5,507

Re: Lag Function

You're using the wrong tool for the job.  You could possibly get it to work, but LAG is more complicated than it looks.  If you want LAG to retrieve the value from the previous observation, it should never be used inside an IF/THEN statement.  A better approach (assuming your data set is sorted):

 

data want;

set have;

by policy_no custid;

if first.custid then new_SMO_flag = SMO_flag;

retain new_SMO_flag;

run;

 

This is probably what you are after (but if not, these would still be the right tools to apply with perhaps a small amount of tweaking).

Super User
Posts: 10,028

Re: Lag Function

CODE NOT TEST

data want;
 set have;
 lag= lag(Smo_Flag)
 if policy_no=Policy_lag   and Cust_iD=Cust_ID_lag   then New_Smo_Flag=lag ;
................

Trusted Advisor
Posts: 1,020

Re: Lag Function

IFN and IFC are often the best way to avoid the most commong pitfalls of lags:

 


data want;
  set have;
   new_smo_flags=ifc(policy_no=lag(policy_no) and cust_id=lag(cust_id),lag(smo_flag),smo_flag);
run;

 

the benefit of ifn (for numeric results) and ifc (for char results) is tha both possible options are evaluated (i.e.the lag queue  is updated) no matter which option is returned.  These functions are syntactically like the IF function in excel.

 

regards,

Mark;

Ask a Question
Discussion stats
  • 3 replies
  • 191 views
  • 0 likes
  • 4 in conversation