topic Re: Repeat rows to fill to fill a specific number of rows in SAS Programming

Repeat rows to fill to fill a specific number of rows

Transcendental — Thu, 04 Nov 2021 17:20:33 GMT

Essentially I need to repeat rows of data till I reach a specific number of rows. For example, say I have 5 rows of data I need to fill 12 rows. That is, I have:

| Index | ID |
:-------:----:
|   1   | A  |
|   2   | B  |
|   3   | C  |
|   4   | D  |
|   5   | E  |

But I need:

| Index | ID |
:-------:-----
|   1   |  A |
|   2   |  B |
|   3   |  C |
|   4   |  D |
|   5   |  E |
|   6   |  A |
|   7   |  B |
|   8   |  C |
|   9   |  D |
|  10   |  E |
|  11   |  A |
|  12   |  B |

This is going to be on a much larger scale. The index does not have to be included. I can think of how to do this in R or Python, but I have very minimal experience in SAS.

Re: Repeat rows to fill to fill a specific number of rows

Reeza — Thu, 04 Nov 2021 17:25:20 GMT

How would you do it in R?

Re: Repeat rows to fill to fill a specific number of rows

Transcendental — Thu, 04 Nov 2021 17:45:35 GMT

Honestly if I were doing this in R I would be do something completely different for the task at hand because R has better capabilities for data structures. But if I just needed to do something like this, I would use a for loop. I know there are DO loops in SAS but I would take advantage of easy indexing in R: I could say table_name[2, 4] and it would give me the contents of the cell in the second row and fourth column. So in a loop I could do something like table_name[i, 3] = .... to add to the third column.

As far as I'm aware there's no equivalency of that in SAS.

Re: Repeat rows to fill to fill a specific number of rows

Tom — Thu, 04 Nov 2021 17:55:03 GMT

What is the source of data you want use?

What is the source of the number of rows you want to create?

Why the heck would you ever want to do this?

Anyway it seems simple enough. Let's create the source dataset.

data have;
  input id $ @@;
cards;
A B C D E
;

Now let's keep re-reading it until we get to 12 observations.

data want;
  if _n_>12 then stop;
  point=1+mod(_n_-1,nobs);
  set have point=point nobs=nobs;
run;

Results:

Re: Repeat rows to fill to fill a specific number of rows

Tom — Thu, 04 Nov 2021 17:59:55 GMT

Do you just want to generate that dataset from a program?

data want ;
  length index 8 id $1 ;
  index=0;
  do until(index>=12);
    do id='A','B','C','D','E';
       index+1;
       output;
       if index >=12 then leave;
    end;
  end;
run;

Re: Repeat rows to fill to fill a specific number of rows

Reeza — Thu, 04 Nov 2021 18:01:49 GMT

I program in both languages as well, so seeing your solution in R would help me to point out the similar or relevant constructs in SAS but your question is pretty straightforward so someone can definitely help you answer it anyways.

Re: Repeat rows to fill to fill a specific number of rows

data_null__ — Thu, 04 Nov 2021 18:28:17 GMT

@Transcendental wrote:
Honestly if I were doing this in R I would be do something completely different for the task at hand because R has better capabilities for data structures. But if I just needed to do something like this, I would use a for loop. I know there are DO loops in SAS but I would take advantage of easy indexing in R: I could say table_name[2, 4] and it would give me the contents of the cell in the second row and fourth column. So in a loop I could do something like table_name[i, 3] = .... to add to the third column.

As far as I'm aware there's no equivalency of that in SAS.

Are you are asking the wrong question?

data _12;
   if _n_ gt 12 then stop;
   set have have have;
   run;

Re: Repeat rows to fill to fill a specific number of rows

Transcendental — Thu, 04 Nov 2021 19:29:23 GMT

Essentially, I'm creating a snake draft, but there's going to be roughly 50+ users (via usernames in one table), changing weekly. And the source of the number of rows (players that can be chosen, in a different table) are also changing weekly.

This is very helpful! Thank you!

Re: Repeat rows to fill to fill a specific number of rows

ballardw — Thu, 04 Nov 2021 20:12:35 GMT

@Transcendental wrote:
Essentially, I'm creating a snake draft, but there's going to be roughly 50+ users (via usernames in one table), changing weekly. And the source of the number of rows (players that can be chosen, in a different table) are also changing weekly.

This is very helpful! Thank you!

That says TWO tables (at least) are involved. Which is quite different conceptually than what you show in the original question.

So why do you ask about adding a fixed number of rows? Perhaps the data with a proper join between the two tables takes care of things.

Or look at Proc IML

Re: Repeat rows to fill to fill a specific number of rows

Transcendental — Thu, 04 Nov 2021 20:58:09 GMT

Because I only needed help with one aspect of an overarching goal. I didn't want to overcomplicate my question by adding in every single detail when I can extrapolate to my larger goal.

Re: Repeat rows to fill to fill a specific number of rows

Reeza — Thu, 04 Nov 2021 21:07:52 GMT

You can and will very likely get to your end solution but you'll hate SAS at the end because you'll be using it inefficiently.

Re: Repeat rows to fill to fill a specific number of rows

Ksharp — Fri, 05 Nov 2021 11:52:01 GMT

As @ballardw said, you should consider SAS/IML . It is very like R language.

data have;
  input id $ @@;
cards;
A B C D E
;

proc iml;
use have;
read all var _all_ into x[c=vname];
close;
want=shape(x,12,ncol(x));
print want[c=vname l=''];
quit;