topic Re: Lag Function in SAS Programming

Lag Function

SBRVamsi — Tue, 15 Nov 2016 16:28:19 GMT

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

Re: Lag Function

Astounding — Tue, 15 Nov 2016 17:44:07 GMT

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

Re: Lag Function

Ksharp — Wed, 16 Nov 2016 05:03:02 GMT

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

Re: Lag Function

mkeintz — Wed, 16 Nov 2016 05:52:51 GMT

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;