SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kirstin
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
ashutosh83
Fluorite | Level 6

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;

novinosrin
Tourmaline | Level 20
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;
Kirstin
Fluorite | Level 6
Thanks! Seems so simple when I see how you did it.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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