topic Loop to add values to a variable in SAS Procedures

Loop to add values to a variable

dadrivr — Sat, 03 Mar 2012 19:55:12 GMT

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;

keep id score time;

run;

--------------------

I am new to SAS, so any help would be greatly appreciated. Thanks in advance!

Loop to add values to a variable

art297 — Sat, 03 Mar 2012 20:01:52 GMT

data test2;

set test;

by id;

if first.id then time=1;

else time+1;

run;

Re: Loop to add values to a variable

MikeZdeb — Sat, 03 Mar 2012 20:05:29 GMT

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;

Re: Loop to add values to a variable

art297 — Sat, 03 Mar 2012 20:14:43 GMT

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

Re: Loop to add values to a variable

Haikuo — Sat, 03 Mar 2012 20:14:44 GMT

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

Re: Loop to add values to a variable

dadrivr — Sat, 03 Mar 2012 20:32:17 GMT

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

Re: Loop to add values to a variable

dadrivr — Sat, 03 Mar 2012 22:09:55 GMT

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!

Re: Loop to add values to a variable

art297 — Sat, 03 Mar 2012 22:22:08 GMT

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

Loop to add values to a variable

dadrivr — Sat, 03 Mar 2012 22:25:07 GMT

Ah, that makes perfect sense. Much appreciated.

Loop to add values to a variable

PGStats — Sun, 04 Mar 2012 02:04:23 GMT

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;

Loop to add values to a variable

art297 — Sun, 04 Mar 2012 03:42:59 GMT

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