Split(insert) rows acording values in column

[bob021]

Hi All
Can anybody help me.

My data:
no part qty
1 A100 2
5 B100 3
25 A100 1
35 B100 2

This is what I want as result:
no part qty
1 A100 1
1 A100 1
5 B100 1
5 B100 1
5 B100 1
25 C100 1
35 B100 1
35 B100 1

In first row is “no” =1 ,” part” = A100 and “qty” =2. I want to split in to two (this number depends on number in “qty” column) rows and change “qty” to 1. Second row will be split in to 3 rows bwcause “qty” is 3. Third row don’t need any change because “qty” is 1 etc.
Thank you in advance
Bob

[polingjw]

[pre]
data have;
input no part $ qty;
datalines;
1 A100 2
5 B100 3
25 A100 1
35 B100 2
;
run;

data want(keep=no part qty);
set have(rename=(qty=oldqty));
retain qty 1;
do i=1 to oldqty;
output;
end;
run;
[/pre]

[ballardw]

I'm not sure what analysis you may be doing but if it may be that you could leave the data as is and use the variable QTY as either a WEIGHT or FREQ variable.

Proc freq;
table no*part;
weight qty;
run;

Otherwise;

in a data step

Data newdata;
set mydata;
max=qty;
Do i= 1 to MAX;
qty=1;
output;
end;
drop i max;
run;

[bob021]

Hi Polingjw and Ballardw
Thank you for your responses. Both solutions working like a charm.

Thanks again.
Bob

[TRCWard]

I have a similar problem, so I was happy to find this post!  However, mine is a little bit different since I want to extra rows to be blank except for the identifier column.  For example, starting with Table HAVE:

Name	Date	Data Columns
A	Jan	X
A	Feb	Y
A	Feb	Z
B	Jan	A

I'm trying to get to Table WANT:

Name	Date	Data Columns
A	Jan	X
A	Jan	.
A	Feb	Y
A	Feb	Z
B	Jan	A

The second row in Table WANT is the "inserted row" with missing values in the Data Columns.  Using your code and a column that indicates when and how many rows to insert, I can insert the wanted extra row but the value in Data Columns is X.  Now I want to set X to missing, but only for the added row.

Help?

[Haikuo]

Since I have the ready-to-go sample set from polingjw , only a minor change to his/her code will do.here is what I came up with:

data have;

input no part $ qty;

datalines;

1 A100 2

5 B100 3

25 A100 1

35 B100 2

;

run;

data want(keep=no part qty);

     set have(rename=(qty=oldqty));

     retain qty 1;

     do _n_=1 to oldqty;

     part=ifc(_n_=1,part,'');

          output;

     end;

run;

proc print;run;

Regards,

Haikuo

[ballardw]

On the code where you are adding the row follow with something like "VAR = .;" before the additional output.

I suspect you have a logical condition being and if you are useing an IF - THEN structure to create the additional row you may need to use a DO - END to get the assignment in right place. If you have a lot of variables an array could be used.

An example of your almost working code would provide a better start point.