Flag a new address within a sublocation group

[dal233]

POL_NBR_ID	POL_EFF_DT	Location	SubLocation	BUS_DESC	SUBLOC_ADDR1	SUBLOC_ADDR2	CITY	building_age	Switch   Location Flag
1	06/29/2005	1	1	REAL ESTATE MANAGEMENT	8550  Pearl		CHICAGO
1	06/29/2006	1	1	REAL ESTATE MANAGEMENT	8550  Pearl		CHICAGO
1	06/29/2007	1	1	OFFICE BLDG.	8550  Pearl		CHICAGO
1	06/29/2008	1	1	OFFICE BLDG.	8550  Pearl		CHICAGO
1	06/29/2009	1	1	OFFICE BLDG.	8550  Pearl		CHICAGO	39
1	06/29/2010	1	1	OFFICE BLDG.	8550  Pearl		CHICAGO	40
1	06/29/2011	1	1	MEDICAL OFFICE BLDG.	2401 Asylum Way		GLENVIEW	10	1
1	06/29/2012	1	1	MEDICAL OFFICE BLDG.	2401 Asylum Way		GLENVIEW	13

I hope the formatting of the above table comes out OK.  I couldn't upload the excel file so just had to paste it in.

What you see above is a dataset with Policy Number, Pol. Eff. Dt, Location, Sublocation and Subloc Address 1 (amongst other less important things).

What I am trying to do is create SAS code to generate a 1 in the "Switch Location Flag" field above.  Notice how the flag appears when the sublocation stays the same, but there is just a change in physical address, as you can see in the Subloc Address 1 field.

It's a pity I can't upload an Excel file here so as that the data is easily portable, but hopefully you get what I mean and can help out.

[Astounding]

It's not 100% clear what the proper sorted order is for your data.  To illustrate the solution, I'll assume it can be sorted by POL_NBR_ID, LOCATION, SUBLOCATION, and POL_EFF_DT.  Even if that's wrong, this will give you the general idea.

The set-up

proc sort data=have;

  by POL_NBR_ID location sublocation pol_eff_dt;

run;

The implementation:

data want;

  set have;

  by pol_nbr_id location sublocation subloc_addr1 notsorted;

  if first.subloc_addr1 then switch=1;

run;

Good luck.

[dal233]

That's not quite what I want.  In that case it will not only flag the change in subloc_addr, but also the first instance of the sublocation (i.e the first "1" above).  I don't want to flag the first instance.

[Astounding · Accepted solution]

Ah, that's right.  A slight change:

if first,.subloc_addr1=1 and first.pol_nbr_id=0 then switch=1;

Again, depending on what you need, it might be:

if first.subloc_addr1=1 and first.sublocation=0 then switch=1;

[dal233]

I do believe that is it!  Thanks.

[dal233]

The sort order is Pol Nbr, Pol Eff. Dt., Location and Sublocation.  And I would like to keep it in that order.

[Astounding]

You should still be OK with that sorted order.

proc sort;

by Pol_nbr_id Pol_Eff_dt location sublocation;

run;

The DATA step that follows would not have to change.