DATA Step, Macro, Functions and more

How do I insert one or many copies of a row based on a condition while incrementing a variable

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

How do I insert one or many copies of a row based on a condition while incrementing a variable

I have a set of data that tells me the last unit completed (unit_comp_order) and the final required unit (max_unit_order) for each course enrolment.  I want to add an incremental number of rows based on the difference between unit_comp_order and max_unit_order.  My sample data is this:

 

data have;
infile datalines delimiter=',';
input id $ course $ unit_comp_order max_unit_order;
datalines;
10001,EDU,6,12
10002,EDU,3,12
;
run;

 

What I have is this:

 

id                 course     unit_comp_order     max_unit_order

10001          EDU         6                                12

10002          EDU         3                                12

 

But i want this:

 

id                 course     unit_comp_order     max_unit_order

10001          EDU         6                                12

10001          EDU         7                                12

10001          EDU         8                                12

10001          EDU         9                                12

10001          EDU         10                              12

10001          EDU         11                              12

10001          EDU         12                              12

10002          EDU         3                                12

10002          EDU         4                                12

10002          EDU         5                                12

10002          EDU         6                                12

10002          EDU         7                                12

10002          EDU         8                                12

10002          EDU         9                                12

10002          EDU         10                              12

10002          EDU         11                               12

10002          EDU         12                               12

 

I tried this to insert the number of blank rows based on the difference between unit_comp_order and max_unit_order but it returned the following error 'ERROR: Array subscript out of range at line 31 column 19.'  and it didn't deal with retaining the other values of id and course.

 

data test; set have;
by id course;
array all _numeric_;
/* output;*/
if unit_comp_order < max_unit_order then
do i= unit_comp_order+1 to max_unit_order;
call missing(all);
unit_comp_order=i;
output;
end;
run;

 

I also tried this which inserted the correct number of rows but doesn't retain or increment the required values.  I'm sure there is a way to avoid doing a separate if statement for each value of 'rows_to_add'.

 

data test;
set have; rows_to_add=max_unit_order-unit_comp_order; run;

data test; set test;
output;

if rows_to_add =9 then do;
call missing(of _all_);


do i = 1 to 9;
output;
end;
end;
if rows_to_add =6 then do;
call missing(of _all_);


do i = 1 to 6;
output;
end;
end;
run;

 

I'm using SAS 9.04. 

Any help much appreciated!


Accepted Solutions
Solution
‎05-05-2018 03:16 AM
Super User
Posts: 2,073

Re: How do I insert one or many copies of a row based on a condition while incrementing a variable

data have;
infile datalines delimiter=',';
input id $ course $ unit_comp_order max_unit_order;
datalines;
10001,EDU,6,12
10002,EDU,3,12
;
run;

 
data want;
set have;
do unit_comp_order=unit_comp_order to max_unit_order;
output;
end;
run;

View solution in original post


All Replies
Super User
Super User
Posts: 9,866

Re: How do I insert one or many copies of a row based on a condition while incrementing a variable

Something like:

data have;
infile datalines delimiter=',';
input id $ course $ unit_comp_order max_unit_order;
datalines;
10001,EDU,6,12
10002,EDU,3,12
;
run;

data test (rename=(i=unit_comp_order));
set have (rename=(unit_comp_order=b));
array tmp _numeric_;
do i=b to max_unit_order;
if i > 1 then call missing(of tmp{*});
output;
end;
run;
Senior User
Posts: 1

Re: How do I insert one or many copies of a row based on a condition while incrementing a variable

This works for me :

 

data test;
set have;
do i=unit_comp_order to max_unit_order;
output;
end;
drop unit_comp_order;
rename i=unit_comp_order;
run;

New Contributor
Posts: 4

Re: How do I insert one or many copies of a row based on a condition while incrementing a variable

Posted in reply to ashutosh83
Thanks - this worked too!
New Contributor
Posts: 4

Re: How do I insert one or many copies of a row based on a condition while incrementing a variable

Thanks this works!
Solution
‎05-05-2018 03:16 AM
Super User
Posts: 2,073

Re: How do I insert one or many copies of a row based on a condition while incrementing a variable

data have;
infile datalines delimiter=',';
input id $ course $ unit_comp_order max_unit_order;
datalines;
10001,EDU,6,12
10002,EDU,3,12
;
run;

 
data want;
set have;
do unit_comp_order=unit_comp_order to max_unit_order;
output;
end;
run;
New Contributor
Posts: 4

Re: How do I insert one or many copies of a row based on a condition while incrementing a variable

Posted in reply to novinosrin
Thanks! Seems so simple when I see how you did it.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 164 views
  • 2 likes
  • 4 in conversation