topic Indentifying observations that enter or leave a panel in SAS Programming

Indentifying observations that enter or leave a panel

as_methodology — Mon, 17 Sep 2018 17:20:05 GMT

Hello

I have an panel and each month I receive a list of all the persons that were in that panel in a that month.Each obeservation has variable indicating the year and month (yyyymm) as well as an ID. What I am trying to do is to generate a list of observations that have entered or left the panel in any given month.

I am very thankful for any ideas/suggestions.

Re: Indentifying observations that enter or leave a panel

SuryaKiran — Mon, 17 Sep 2018 17:29:52 GMT

It all depends on the data you have. Please provide with some sample data.

Re: Indentifying observations that enter or leave a panel

as_methodology — Tue, 18 Sep 2018 05:10:38 GMT

Hi Suryakiran

Thanks for the reply. I don't know if this helps, but this is basically what I have for August 2018:

YEAR_MONTH	ID	City	More Info
201808	180764	Eschen	…
201808	332011	Eschen	…
201808	901917	Eschen	…
201808	849856	Vaduz	…
201808	430057	Vaduz	…
201808	721939	Schaan	…
201808	78890	Schaan	…
201808	234806	Planken	…

What I tried so far is to create an extra column with month t-1:

YEAR_MONTH	YEAR_MONTH_1	ID	City	More Info
201808	201807	180764	Eschen	…
201808	201807	332011	Eschen	…
201808	201807	901917	Eschen	…
201808	201807	849856	Vaduz	…

And then using catx to generate an unique month-person identifier for month t and month t-1.

YEAR_MONTH	YEAR_MONTH_1	ID	ID_MONTH	ID_MONTH_1	City	More Info
201808	201807	180764	180764x201808	180764x201807	Eschen	…
201808	201807	332011	332011x201808	332011x201807	Eschen	…
201808	201807	901917	901917x201808	901917x201807	Eschen	…
201808	201807	849856	849856x201808	849856x201807	Vaduz	…

Then using proc sql to join it up with itself

PROC SQL;
   CREATE TABLE WORK.TEST AS
   SELECT *,
           t2.ONE
      FROM WORK.TEST t1
           LEFT JOIN WORK.TEST t2 ON (t1.YEAR_MONTH = t2.YEAR_MONTH_1);
QUIT;

where ONE is a column containing only a 1 for each observation. The idea being that maches will result in a 1, whereas if there was no match it will create a missing value. Technically, it should work, but it hasn't yet. So I was also wonderig whether there would be an easier approach.

Thanks a lot already, Simon

Re: Indentifying observations that enter or leave a panel

as_methodology — Tue, 18 Sep 2018 15:25:15 GMT

Update: It works the way I mentioned in my previous post. It's a little tedious but it works.