Solved
Contributor
Posts: 41

# Generate rows per a column value

Table Input:

ID Name Num_Chances

Carmen 5

Sue       2

Jack      3

John      1

Need 1 row for each person based on num_chances:

Carmen

Carmen

Carmen

Carmen

Carmen

Sue

Sue

Jack

Jack

Jack

John

Accepted Solutions
Solution
‎12-02-2015 03:51 PM
Posts: 3,186

## Re: Generate rows per a column value

```data have;
input name:\$10. Num_Chances;
cards;
Carmen 5
Sue       2
Jack      3
John      1
;

data want;
set have;
do _n_=1 to num_chances;
output;
end;
keep name;
run;```

All Replies
Solution
‎12-02-2015 03:51 PM
Posts: 3,186

## Re: Generate rows per a column value

```data have;
input name:\$10. Num_Chances;
cards;
Carmen 5
Sue       2
Jack      3
John      1
;

data want;
set have;
do _n_=1 to num_chances;
output;
end;
keep name;
run;```
Contributor
Posts: 41

## Re: Generate rows per a column value

Help!

I was getting the output I wanted after posting and now I am not (unsure what I changed).  The following results are no rows:

data Rounded_Chances;
input Employee_ID Suffix:\$10.  Num_Chances_Rounded;

data All_Rows;
set Rounded_Chances;
do _n_=1 to Num_Chances_Rounded;
output;
end;
keep Employee_ID Suffix Num_Chances_Rounded;

Super Contributor
Posts: 285

## Re: Generate rows per a column value

Haikuo, may be I am missing something important. When data step reads Sue, isn't _n_ becomes 2. What is logic behind "do group" starting form _n_=1? I am assuming when _n_=2 it is asking to iterate to 2 (Num_chances). So there is interplay of two kinds of _n_? when I put index "i" instead of _n_, it gives same result.
Posts: 3,186

## Re: Generate rows per a column value

I admit this is not conventional use of '_n_' and it does inherit some risks if you are not completely sure about its inandout. The only reason I choose to do so is my laziness. The normal way to do it is to initiate a non-automatic variable as index, then drop it after the use.

Here is what going on:

1. _n_ is automatic numeric variable that will not go to the output table, it does not need to be dropped, a potential to save some typing.

2. You already knew that _n_ will +1 for every data step implicit loop. What you are not aware is that it restored its counts from last loop and  plus 1 when new loop starts.

3. Anywhere between data step implicit loops, _n_ is just a normal numeric variable that can be used as one, meaning you can assign values and use it as a index for a inner loop.  When next round of data step implicit loop starts, _n_ will be back into playing its meant-to- be roles.

Super Contributor
Posts: 285

## Re: Generate rows per a column value

Thanks for clarification.
Contributor
Posts: 41

## Re: Generate rows per a column value

I was getting the output I wanted after posting and now I am not (unsure what I changed).  The following results are no rows:

data Rounded_Chances;
input Employee_ID Suffix:\$10.  Num_Chances_Rounded;

data All_Rows;
set Rounded_Chances;
do _n_=1 to Num_Chances_Rounded;
output;
end;
keep Employee_ID Suffix Num_Chances_Rounded;
run;

Posts: 3,186

## Re: Generate rows per a column value

What does your data look like?

Contributor
Posts: 41

## Re: Generate rows per a column value

Did not return any rows.

Posts: 3,186

Contributor
Posts: 41

## Re: Generate rows per a column value

Example

 Prefix First_Name Middle_Name Last Name Suffix Employee_ID Total_Pledge Ms. Jack K Abbott 11 50 Ms. Tome P Abernethy 23 45 Mr. Sue L Abeyta 23 20 Ms. Erica L Abondolo 45 250 Ms. Alana C Adam 45 15

:

Posts: 3,186

## Re: Generate rows per a column value

[ Edited ]

Are you looping upon the number of 'Total_Pledge'? If so, make sure it is numeric. If not, convert it before the looping:

``num_pledge=input(Total_Pledge,best32.);``

Then loop upon 'num_pledge'.

Contributor
Posts: 41

## Re: Generate rows per a column value

Thanks, everything looks good now.!

🔒 This topic is solved and locked.