Loop to add values to a variable

[dadrivr]

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!

[art297]

data test2;

  set test;

  by id;

  if first.id then time=1;

  else time+1;

run;

[MikeZdeb]

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;

[art297]

Mike, FWIW, it is actually about 10% more efficient using the if then combination.

[Haikuo]

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

[dadrivr]

All of your responses were very helpful and speedy.  Thanks everyone!

[dadrivr]

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!

[art297]

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

[dadrivr]

Ah, that makes perfect sense.  Much appreciated.

[PGStats]

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

[art297]

Although proc rank would only be applicable if score is relevant.  If not, and only order was relevantt, it would provide the wrong answer.