topic Re: Generate rows per a column value in SAS Enterprise Guide

Generate rows per a column value

carmendee — Wed, 02 Dec 2015 20:13:38 GMT

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

Sue

Jack

John

Re: Generate rows per a column value

Haikuo — Wed, 02 Dec 2015 20:29:27 GMT

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;

Re: Generate rows per a column value

carmendee — Wed, 16 Dec 2015 16:35:04 GMT

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;

Re: Generate rows per a column value

Haikuo — Wed, 16 Dec 2015 16:53:26 GMT

What does your data look like?

Re: Generate rows per a column value

carmendee — Wed, 16 Dec 2015 17:00:49 GMT

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:

Re: Generate rows per a column value

carmendee — Wed, 16 Dec 2015 18:20:22 GMT

Did not return any rows.

Re: Generate rows per a column value

Haikuo — Wed, 16 Dec 2015 18:21:44 GMT

I mean your incoming data.

Re: Generate rows per a column value

carmendee — Wed, 16 Dec 2015 18:22:09 GMT

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

Re: Generate rows per a column value

Haikuo — Wed, 16 Dec 2015 18:28:15 GMT

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'.

Re: Generate rows per a column value

carmendee — Wed, 16 Dec 2015 20:22:11 GMT

Thanks, everything looks good now.!

Re: Generate rows per a column value

SAS_inquisitive — Thu, 17 Dec 2015 15:26:25 GMT

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.

Re: Generate rows per a column value

Haikuo — Thu, 17 Dec 2015 15:50:08 GMT

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.

Re: Generate rows per a column value

SAS_inquisitive — Thu, 17 Dec 2015 16:15:11 GMT

Thanks for clarification.