11-15-2016 11:28 AM
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
11-15-2016 12:44 PM
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):
by policy_no custid;
if first.custid then new_SMO_flag = SMO_flag;
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).
11-16-2016 12:52 AM
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.