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.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.