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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.