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.
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
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.
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.
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.
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.