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.
It all depends on the data you have. Please provide with some sample data.
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
Update: It works the way I mentioned in my previous post. It's a little tedious but it works.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.