topic Re: Trying to condition on when a variable changes from 0 to 1 or 1 to 0 in SAS Programming

Trying to condition on when a variable changes from 0 to 1 or 1 to 0

jss539 — Fri, 18 Sep 2020 14:27:36 GMT

I have a dataset on properties, where each property has several years of observations. With that, each property is assigned as either independent or branded. Some properties, though, switched from independent to branded (or vice versa) in one of their years. Right now, I have a "branded" variable that is either 0 or 1. For example, property 1 could have data from 2002 to 2011 and be branded (branded=1) in all of the years. Property 2 could have data from 2005 to 2012 and be independent (branded=0) for all the years. Property 3, which is the one I would be interested in, could be branded (branded=1) from 2000 to 2009, then independent (branded=0) from 2010 and on. My end goal is to create 3 more summy variables: 1 that tells if the property switched, 1 that is tells is it switched from branded to independent, and another that tells if it switched from independent to branded.

The variables I have to work with are: propertyID, address, and branded.

What I've tried so far:

First, I successfully made a new dataset, the one I'm using below, that is only of hotels that changed. I just need to see which year and which way they changed.

proc sort data=changed_props;
by address year;
run;

/*This is where I'm running into an issue.  I am only looking for hotels that changed from branded=0 to branded=1.
What I want is to tell when they changed either way.  I thought about doing 2 of these blocks of code and merging them,
But I worry that I'll be getting the first year of data for each property rather than just the year it changed*/

data changed_props_temp;
set changed_props;
by address;
where branded>0;
if first.address;
year_changed = year;
change_year = 1;
keep address year_changed change_year branded;
run;

*Putting it back;
data changed_props;
merge changed_props changed_props_temp;
by address;
if year = year_changed then change_year=1;
else change_year=0;
run;

In all, what I need is to tell when the branded variable switches from 0 to 1 or 1 to 0 from the past observation with the same address. Thanks for the help.

Re: Trying to condition on when a variable changes from 0 to 1 or 1 to 0

Astounding — Fri, 18 Sep 2020 16:22:45 GMT

Is there any possibility that a property will switch (say from 0 to 1) and then later switch back (say from 1 to 0)? What would you like the result to be for that case?

Re: Trying to condition on when a variable changes from 0 to 1 or 1 to 0

jss539 — Fri, 18 Sep 2020 17:04:08 GMT

Good question. At first I did not think this was possible. But, looking
at the data, there are a few instances of it. I would prefer to be able to
have a result for that as well, but I could go through manually if I needed
to.

Re: Trying to condition on when a variable changes from 0 to 1 or 1 to 0

Astounding — Fri, 18 Sep 2020 19:51:22 GMT

Given that other possibilities exist, I would just try to find the patterns. For example after sorting your data:

data changed_props_temp;
set changed_props;
by address branded notsorted;
length pattern $ 9;
if first.address then pattern = ' ';
if first.branded then pattern = catx(' ', pattern, branded);
if last.address;
retain pattern;
keep address pattern;
run;

Not sure if you want to keep more variables, but for now this tells you what patterns of branded exist over time. You might just run a PROC FREQ on PATTERN after this to see what patterns you will need to plan for.

Re: Trying to condition on when a variable changes from 0 to 1 or 1 to 0

jss539 — Fri, 18 Sep 2020 20:22:08 GMT

This helped me visualize the data, I appreciate the help. An issue I ran
into when checking this, though, is that some properties which changed in
their second year and changed again after that only shows as changing once
in the new "pattern" variable. For example, one property started with 1,
switched to 0 for two years, then back to 1. The new variable only shows
"1 0".

Re: Trying to condition on when a variable changes from 0 to 1 or 1 to 0

Astounding — Sat, 19 Sep 2020 03:03:49 GMT

That shouldn't happen. Please post the log from that DATA step so I can verify the logic that it contains.