topic Re: Flag a new address within a sublocation group in SAS Programming

Flag a new address within a sublocation group

dal233 — Fri, 07 Feb 2014 14:46:18 GMT

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.

Re: Flag a new address within a sublocation group

Astounding — Fri, 07 Feb 2014 15:44:40 GMT

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.

Re: Flag a new address within a sublocation group

dal233 — Fri, 07 Feb 2014 15:53:46 GMT

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.

Re: Flag a new address within a sublocation group

Astounding — Fri, 07 Feb 2014 15:57:18 GMT

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;

Re: Flag a new address within a sublocation group

dal233 — Fri, 07 Feb 2014 15:58:21 GMT

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

Re: Flag a new address within a sublocation group

dal233 — Fri, 07 Feb 2014 16:02:19 GMT

I do believe that is it! Thanks.

Re: Flag a new address within a sublocation group

Astounding — Fri, 07 Feb 2014 16:02:42 GMT

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.