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.
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;
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.
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.
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;
I do believe that is it! Thanks.
The sort order is Pol Nbr, Pol Eff. Dt., Location and Sublocation. And I would like to keep it in that order.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.