DATA Step, Macro, Functions and more

Flag a new address within a sublocation group

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Flag a new address within a sublocation group

POL_NBR_IDPOL_EFF_DTLocationSubLocationBUS_DESCSUBLOC_ADDR1SUBLOC_ADDR2CITYbuilding_ageSwitch
  Location Flag
106/29/200511REAL ESTATE MANAGEMENT8550  PearlCHICAGO
106/29/200611REAL ESTATE MANAGEMENT8550  PearlCHICAGO
106/29/200711OFFICE BLDG.8550  PearlCHICAGO
106/29/200811OFFICE BLDG.8550  PearlCHICAGO
106/29/200911OFFICE BLDG.8550  PearlCHICAGO39
106/29/201011OFFICE BLDG.8550  PearlCHICAGO40
106/29/201111MEDICAL OFFICE BLDG.2401 Asylum WayGLENVIEW101
106/29/201211MEDICAL OFFICE BLDG.2401 Asylum WayGLENVIEW13

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.


Accepted Solutions
Solution
‎02-07-2014 10:57 AM
Super User
Posts: 5,081

Re: Flag a new address within a sublocation group

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;

View solution in original post


All Replies
Super User
Posts: 5,081

Re: Flag a new address within a sublocation group

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.

Occasional Contributor
Posts: 13

Re: Flag a new address within a sublocation group

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.

Solution
‎02-07-2014 10:57 AM
Super User
Posts: 5,081

Re: Flag a new address within a sublocation group

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;

Occasional Contributor
Posts: 13

Re: Flag a new address within a sublocation group

I do believe that is it!  Thanks.

Occasional Contributor
Posts: 13

Re: Flag a new address within a sublocation group

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

Super User
Posts: 5,081

Re: Flag a new address within a sublocation group

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 277 views
  • 0 likes
  • 2 in conversation