topic Re: Split(insert) rows acording values in column in SAS Procedures

Split(insert) rows acording values in column

bob021 — Thu, 02 Jun 2011 14:44:36 GMT

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

Re: Split(insert) rows acording values in column

polingjw — Thu, 02 Jun 2011 14:55:24 GMT

[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]

Re: Split(insert) rows acording values in column

ballardw — Thu, 02 Jun 2011 18:30:22 GMT

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;

Re: Split(insert) rows acording values in column

bob021 — Fri, 03 Jun 2011 09:33:23 GMT

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

Thanks again.
Bob

Re: Split(insert) rows acording values in column

TRCWard — Tue, 20 Mar 2012 19:53:27 GMT

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?

Re: Split(insert) rows acording values in column

Haikuo — Tue, 20 Mar 2012 20:31:50 GMT

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

Re: Split(insert) rows acording values in column

ballardw — Tue, 20 Mar 2012 20:37:36 GMT

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.