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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.