Desktop productivity for business analysts and programmers

Generate rows per a column value

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

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
Respected Advisor
Posts: 3,124

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;

View solution in original post


All Replies
Solution
‎12-02-2015 03:51 PM
Respected Advisor
Posts: 3,124

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;

Regular Contributor
Posts: 233

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.
Respected Advisor
Posts: 3,124

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.

Regular Contributor
Posts: 233

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;    

Respected Advisor
Posts: 3,124

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.

 

Respected Advisor
Posts: 3,124

Re: Generate rows per a column value

I mean your incoming data.

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

:

 

Respected Advisor
Posts: 3,124

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.

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

Discussion stats
  • 12 replies
  • 640 views
  • 3 likes
  • 3 in conversation