Hi,
I have a data set in a structure similar to the following (tall format):
id score
100 10
100 13
100 16
100 19
101 13
101 14
101 15
101 16
103 20
103 22
103 24
103 26
I want to add a time variable so that the new data set appears as below:
id score time
100 10 1
100 13 2
100 16 3
100 19 4
101 13 1
101 14 2
101 15 3
101 16 4
103 20 1
103 22 2
103 24 3
103 26 4
Here is my attempt, but it doesn't produce the expected output, and I'm not sure why.
--------------------
data test;
input id score;
datalines;
100 10
100 13
100 16
100 19
101 13
101 14
101 15
101 16
103 20
103 22
103 24
103 26
;
run;
data test2;
set test;
do i=1 to 3;
do j=1 to 4;
time=j;
output;
end;
end;
keep id score time;
run;
--------------------
I am new to SAS, so any help would be greatly appreciated. Thanks in advance!
data test2;
set test;
by id;
if first.id then time=1;
else time+1;
run;
hi ... same idea as Art, but no IF-THEN ...
data x;
input id score @@;
datalines;
100 10 100 13 100 16 100 19
101 13 101 14 101 15 101 16
103 20 103 22 103 24 103 26
;
data x;
set x;
by id;
time + (-first.id * time) + 1;
run;
Mike, FWIW, it is actually about 10% more efficient using the if then combination.
If you want to play with do-loop, here is another approach:
data want;
do time=1 by 1 until (last.id);
set test;
by id;
output;
end;
run;
Regards,
Haikuo
All of your responses were very helpful and speedy. Thanks everyone!
Quick follow-up question. How can I increment time conditional on two variables (ID and Group). For example, if I have the following data set:
id score group
100 10 A
100 13 A
100 16 B
100 19 B
101 13 A
101 14 A
101 15 C
101 16 C
103 20 A
103 22 A
103 24 B
103 26 C
I'd like to create a time variable that increments with evey new combination of ID and group. Here is the data set I want:
id score group time
100 10 A 1
100 13 A 2
100 16 B 1
100 19 B 2
101 13 A 1
101 14 A 2
101 15 C 1
101 16 C 2
103 20 A 1
103 22 A 2
103 24 B 1
103 26 C 1
Here is an example data set that I'd like to convert (to the above structure):
data test;
input id score group;
datalines;
100 10 A
100 13 A
100 16 B
100 19 B
101 13 A
101 14 A
101 15 C
101 16 C
103 20 A
103 22 A
103 24 B
103 26 C
;
run;
Thanks again!
Same way. Just change your by statement to read: by id group
and
change the condition to be: if first.group then time=1
Of course, your data first has to be sorted by: id group
Ah, that makes perfect sense. Much appreciated.
Good old proc rank does exactly what you want. No fuss.
data have;
input id score group $;
datalines;
100 10 A
100 13 A
100 16 B
100 19 B
101 13 A
101 14 A
101 15 C
101 16 C
103 20 A
103 22 A
103 24 B
103 26 C
;
proc rank data=have out=want;
by id group;
var score;
ranks time;
run;
PG
Although proc rank would only be applicable if score is relevant. If not, and only order was relevantt, it would provide the wrong answer.
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.