How can I do this in SAS

[Teamtim]

I have a data in this format:

id       years sex state

100002  0       0    1

100002  1       0    1

100002  2       0    2

100002  3       0    2

100002  4       0    2

100002  5       0    3

100004  0       1   1

100004  1.2    1   1

100004  2.4    1   1 

and I want to create additional new three columns ( "start" & "end" out of "state", and "time" out of "age") such as the one below

id          years sex state start End Time

100002    0       0    1       1    1      1

100002    1       0    1       1     2     2

100002    2       0    2       2     2     3

100002    3       0    2       2     2     4

100002    4       0    2       2     3     5

100004    0       1   1        1    1   1.2

100004  1.2      1   1         1    1   2.4 

Please is there anyone who can help me out with this in SAS. Thanks I really appreciate.

[AncaTilea]

Your initial data structure is rather confusing.

Your initial data has 6 rows for ID 100002 and 3 rows for 100004.

What is the logic that makes the last row for each ID go away in the data you want?

I can see that for the 'time' variable you are simply bringing up the 'years' variable values, but not if it's zero?

Provide a bit more detail.

Anca.

[Teamtim]

When I re-arrange the "state" variable into "end" and "start" the first year is not not needed any more which such that when the last one is used I then have an extra row which is not needed.

[AncaTilea]

How about this?

assuming your example data set is named have;

data want;

     set have;

     by id;

     if eof1 = 0 then set have (firstobs = 1 keep = state rename = (state = start)) end = eof1;

     else start = .;

     if eof1 = 0 then set have (firstobs = 2 keep = state years

           rename = (state = end years = time)) end = eof1;

     else end= .;

if last.id then delete;

run;

I hope it helps.

Anca.

[Ksharp · Accepted solution]

It makes me confused . what is out of "state", and what is out of "age" ?

data have;
input id       years sex state ;
cards;
100002  0       0    1
100002  1       0    1
100002  2       0    2
100002  3       0    2
100002  4       0    2
100002  5       0    3
100004  0       1   1
100004  1.2    1   1
100004  2.4    1   1 
;
run;
data one two(keep=state years rename=(state=end years=time));
 set have;
 by id;
 start=state;
 if not first.id then output two;
 if not last.id then output one;
run;
data want;
 merge one two;
run;

Xia Keshan