topic Re: How can I do this in SAS in SAS Programming

How can I do this in SAS

Teamtim — Mon, 28 Jul 2014 16:07:41 GMT

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.

Re: How can I do this in SAS

AncaTilea — Mon, 28 Jul 2014 16:18:13 GMT

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.

Re: How can I do this in SAS

Teamtim — Mon, 28 Jul 2014 17:13:46 GMT

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.

Re: How can I do this in SAS

AncaTilea — Mon, 28 Jul 2014 17:58:21 GMT

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.

Re: How can I do this in SAS

Ksharp — Tue, 29 Jul 2014 14:28:28 GMT

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