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

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

Kirstin — Thu, 03 May 2018 12:06:15 GMT

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!

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

RW9 — Thu, 03 May 2018 12:24:29 GMT

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;

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

ashutosh83 — Thu, 03 May 2018 13:01:54 GMT

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;

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

novinosrin — Thu, 03 May 2018 14:22:17 GMT

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;

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

Kirstin — Sat, 05 May 2018 07:16:17 GMT

Thanks this works!

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

Kirstin — Sat, 05 May 2018 07:16:32 GMT

Thanks - this worked too!

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

Kirstin — Sat, 05 May 2018 07:17:07 GMT

Thanks! Seems so simple when I see how you did it.