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-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!

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.

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
  • 939 views
  • 2 likes
  • 4 in conversation