BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dal233
Calcite | Level 5
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.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

6 REPLIES 6
Astounding
PROC Star

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
Calcite | Level 5

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
PROC Star

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
Calcite | Level 5

I do believe that is it!  Thanks.

dal233
Calcite | Level 5

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

Astounding
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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