hi,

Just read an interesting article in the Stata Journal(2008, Vol 8, No 4, pp 540-553). The article deals with a data management problem. I thought it might be fun to try to solve it with sas.

The input dataset(one, below) has three vars: old, updated, and date. Each observation represents an event of changing an id from old to updated. Id changes can happen multiple times. For instance, the person, who changed her id from "A" to "B" on Oct 23, 2003, did it again ("B" to "C") in Jul 2006, and again ("C" to "D") in Feb 2007.

The goal is to create two new variables: Recent, which shows the latest id value, and NoOfEvents the number of id changing events, for the person. The output dataset should look like the dataset two, below.

Now, here we have the input data with only 10 id changing events, but the author of the stata journal article says that his input data had 86,000 observations. The point of the article was that initially his stata code took 12 hours to do this. Then he ended up writing a better code that ran in a few seconds.

How would you do this with sas? (And with R, anyone?)

cheers, chang

/* input data set */ data one; input (old updated) ($) date; cards; A B 16001 Q P 16004 E F 16007 G H 16008 X1 X2 16016 X2 X3 16017 P O 16100 O N 16999 B C 17000 C D 17200 ; run;

/* check */ proc print data=one; format date date9.; run;

/* desired dataset, two, that prints: old updated date recent NoOfEvents -------------------------------------------------- A B 16001 D 3 B C 17000 D 3 C D 17200 D 3 E F 16007 F 1 G H 16008 H 1 O N 16999 N 3 P O 16100 N 3 Q P 16004 N 3 X1 X2 16016 X3 2 X2 X3 16017 X3 2 */