BookmarkSubscribeRSS Feed
dadrivr
Calcite | Level 5

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!

10 REPLIES 10
art297
Opal | Level 21

data test2;

  set test;

  by id;

  if first.id then time=1;

  else time+1;

run;

MikeZdeb
Rhodochrosite | Level 12

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
Opal | Level 21

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

Haikuo
Onyx | Level 15

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
Calcite | Level 5

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

dadrivr
Calcite | Level 5

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
Opal | Level 21

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
Calcite | Level 5

Ah, that makes perfect sense.  Much appreciated.

PGStats
Opal | Level 21

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

PG
art297
Opal | Level 21

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

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1202 views
  • 1 like
  • 5 in conversation