Help using Base SAS procedures

Split(insert) rows acording values in column

Reply
Contributor
Posts: 60

Split(insert) rows acording values in column

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
Regular Contributor
Posts: 171

Re: Split(insert) rows acording values in column

[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]
Super User
Posts: 10,497

Re: Split(insert) rows acording values in column

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;
Contributor
Posts: 60

Re: Split(insert) rows acording values in column

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

Thanks again.
Bob
Occasional Contributor
Posts: 7

Re: Split(insert) rows acording values in column

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:

NameDateData Columns
AJanX
AFebY
AFebZ
BJanA

I'm trying to get to Table WANT:

NameDateData Columns
AJanX
AJan.
AFebY
AFebZ
BJanA


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?

Respected Advisor
Posts: 3,124

Re: Split(insert) rows acording values in column

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

Super User
Posts: 10,497

Re: Split(insert) rows acording values in column

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.

Ask a Question
Discussion stats
  • 6 replies
  • 499 views
  • 0 likes
  • 5 in conversation