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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.