longitudinal cohort data manipulation

[Justin_Lang]

Hi SAS community,

I have a longitudinal cohort dataset. I need to keep only participants with urban = 1 at baseline. 

Dataset1

UniqID	year	urban
101	2001	1
101	2002	1
101	2003	1
101	2004	1
102	2001	0
102	2002	0
102	2003	1
102	2004	1
103	2001	0
103	2002	0
103	2003	0
103	2004	1
104	2001	1
104	2002	0
104	2003	0
104	2004	0

Needs to look like this:

UniqID	year	urban
101	2001	1
101	2002	1
101	2003	1
101	2004	1
104	2001	1
104	2002	0
104	2003	0
104	2004	0

Can someone please help me!

Justin

[ballardw · Accepted solution]

---

@Justin_Lang wrote:

Hi SAS community,

I have a longitudinal cohort dataset. I need to keep only participants with urban = 1 at baseline. 

Dataset1

UniqID	year	urban
101	2001	1
101	2002	1
101	2003	1
101	2004	1
102	2001	0
102	2002	0
102	2003	1
102	2004	1
103	2001	0
103	2002	0
103	2003	0
103	2004	1
104	2001	1
104	2002	0
104	2003	0
104	2004	0

Needs to look like this:

UniqID	year	urban
101	2001	1
101	2002	1
101	2003	1
101	2004	1
104	2001	1
104	2002	0
104	2003	0
104	2004	0

Can someone please help me!

Justin

---

See if this gets you started.

/* assumes input set sorted by ID and year*/
data want;
  set have;
  by uniqid ;
  retain flag;
  if first.uniqid then flag= (urban=1);
  if flag;
  drop flag;
run;

From the way you present your example data I suspect that your Urban value might actually be character. If the above code gives you messages about conversion to character messages then likely you should change the urban=1 to urban='1'.

The key part is the BY processing sets an automatic variable referenced as first.uniqid that is true for the first record with the value. The Flag = does a logical comparison of the Urban value an if true will have a value of 1(true) or 0 (false). The Retain statement keeps the value of flag for the next iteration of the data step. The If Flag subsets the data to have only the values where Flag is true.

Since you really wouldn't need the value of flag later I drop it.

You could test the logic of the code without the IF flag; and Drop Flag ; statements to see the result of the logic.

[Justin_Lang]

Thanks for the reply. This seems to have worked for me!