BookmarkSubscribeRSS Feed
SBRVamsi
Fluorite | Level 6

 

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

3 REPLIES 3
Astounding
PROC Star

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

Ksharp
Super User
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 ;
................

mkeintz
PROC Star

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;

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 830 views
  • 0 likes
  • 4 in conversation