SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Adding rows in unbalanced repeated measures, so each case has the same number of rows.

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Adding rows in unbalanced repeated measures, so each case has the same number of rows.

 Hello All,  

 

I think there's a simple solution to this, but nothing I’ve tried is panning out. 

 

The problem: I have repeated measures (test trials) for each individual (i.e., case). They are stacked in a long format(see below).

 

Not every individual has the same number of observations.  They range from 0 to 48, where 0 represents those who failed practice trials and thus didn’t progress onto subsequent trials (e.g., id = 1). 48 represents those who made it to the end of all possible trials (e.g., id = 3). Other individuals were kicked out somewhere between 0 and 48 trials (e.g., Id = 2).

 

I’d like 48 rows for every case, where trials that occur after the case was kicked out are treated as incorrect (i.e., correct = 0).

 

Any thoughts or suggestions would be much appreciated. Thank you in advance.  

 

-dan

 

What I have:

ID

Correct

1

.

2

1

2

1

2

0

2

1

3

1

3

1

3

1

3

1

3

1

3

0

3

3

…48 total rows

 

What I want:

ID

Correct

1

0

1

0

1

…0

1

…48 total rows

2

1

2

1

2

0

2

1

2

…0

2

…48 total rows

3

1

3

1

3

1

3

1

3

0

3

3

…48 total rows

 

 

  


Accepted Solutions
Solution
‎05-22-2017 11:26 AM
PROC Star
Posts: 7,471

Re: Adding rows in unbalanced repeated measures, so each case has the same number of rows.

Posted in reply to coltraned
data want (drop=counter);
  set have;
  by id;
  if first.id then counter=1;
  else counter+1;
  output;
  Correct=0;
  if last.id and counter lt 48 then do i=counter+1 to 48;
    output;
  end;
run;

Art, CEO, AnalystFinder.com

View solution in original post


All Replies
Solution
‎05-22-2017 11:26 AM
PROC Star
Posts: 7,471

Re: Adding rows in unbalanced repeated measures, so each case has the same number of rows.

Posted in reply to coltraned
data want (drop=counter);
  set have;
  by id;
  if first.id then counter=1;
  else counter+1;
  output;
  Correct=0;
  if last.id and counter lt 48 then do i=counter+1 to 48;
    output;
  end;
run;

Art, CEO, AnalystFinder.com

New Contributor
Posts: 2

Re: Adding rows in unbalanced repeated measures, so each case has the same number of rows.

Thanks, Art!

Works perfectly! I can stop banging my head against the wall now!

##- Please type your reply above this line. Simple formatting, no
attachments. -##
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 131 views
  • 0 likes
  • 2 in conversation