BookmarkSubscribeRSS Feed
bob021
Calcite | Level 5
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
6 REPLIES 6
polingjw
Quartz | Level 8
[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
Super User
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
Calcite | Level 5
Hi Polingjw and Ballardw
Thank you for your responses. Both solutions working like a charm.

Thanks again.
Bob
TRCWard
Calcite | Level 5

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?

Haikuo
Onyx | Level 15

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1517 views
  • 0 likes
  • 5 in conversation