## How can I do this in SAS

Solved
Occasional Contributor
Posts: 16

# How can I do this in SAS

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.

Accepted Solutions
Solution
‎07-29-2014 10:28 AM
Super User
Posts: 10,784

## Re: How can I do this in SAS

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

All Replies
Super Contributor
Posts: 543

## Re: How can I do this in SAS

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.

Occasional Contributor
Posts: 16

## Re: How can I do this in SAS

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.

Super Contributor
Posts: 543

## Re: How can I do this in SAS

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.

Solution
‎07-29-2014 10:28 AM
Super User
Posts: 10,784

## Re: How can I do this in SAS

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

🔒 This topic is solved and locked.