SAS iteration through table

I have two tables. The first table contains columns for student: ID, first_name, last_name, book. The second table contains columns for book: title and availability. I need to randomly assign a book title from the second table to each student in the first table. The number of books is limited and specified in the "availability" column.

I tried something like:

%let title;
data student_book;
set first_table;
if missing(book) then do;
call execute(proc sql; select title into :title from second_table where availability > 0 order by rand("uniform"); quit;');
book = "&title";

end;
run;

but it's not working, probably, CALL EXECUTE is evaluated late :(

I'm assuming you want each book copy that is available to have the same probability of being selected.

You have data with one record per book title, and you know the number of copies of the title that are available:

data booksummary ;
  input title $1 availability ;
  cards ;
A 10
B 10
C 5
D 0
;

One way to approach this would be to make a new dataset that has one record per book copy available. So instead of the above four records, you would have 25 records. And you can create a random number for each record, which you can use to sort the records into a random order:

data books (keep=title rand);
  set booksummary ;
  call streaminit(123);
  do i=1 to availability ;
    rand=rand("Uniform");
    output ;
  end ;
run ;

proc sort data=books ;
  by rand ;
run ;

Then when you have the available books in random order, you can just smush the column of book titles onto your column of student names. To 'smush' data you can use the MERGE statement with no BY statement.

options mergenoby=nowarn ; *smush coming ;
data want ;
  merge sashelp.class (keep=name in=a)
        books (keep=title)
  ;
  *no by statement, 1:1 smush ;
  if a ;
run ;
options mergenoby=error ;

call execute happens after the data step. You cannot use the results of call execute within a data step. If you want to pull from second_table, subtract its availability, then randomly pull a new title from the table where availability > 0, one option is to use a macro loop that iterates and updates both tables.

Sample data:

data student_book;
    set sashelp.class;

    select(_N_);
        when(1) book = 'book1';
        when(2) book = 'book2';
        when(3) book = ' ';
        when(4) book = ' ';
        when(5) book = 'book3';
        when(6) book = 'book4';
        when(7) book = ' ';
        when(8) book = ' ';
        otherwise;
    end;

    if(_N_ LE 8);

    keep name book;
run;

data second_table;
    input title$ availability;
    datalines;
book5 1
book6 2
book7 3
book8 4
book9 5
;
run;

Code:

%macro replace_missing_books;
    
    /* Count missing books */
    %let dsid     = %sysfunc(open(student_book(where=(missing(book)))));
    %let nmissing = %sysfunc(attrn(&dsid, nlobsf));
    %let rc       = %sysfunc(close(&dsid));

    %do %until(&nmissing = 0);
       
        /* Pull a random book */
        proc sql noprint outobs=1;
            select title
            into :title trimmed
            from second_table
            where availability > 0
            order by rand('uniform')
            ;
        quit;

        /* Update the first instance of a missing book */
        data student_book;
            modify student_book;
            where missing(book);

            book = "&title";
            replace;
            stop;
        run;

        /* Update book availability */
        data second_table;
            modify second_table;
            where title = "&title";
            availability = availability-1;
        run;

        /* Check how many missing books are left */
        %let dsid     = %sysfunc(open(student_book(where=(missing(book)))));
        %let nmissing = %sysfunc(attrn(&dsid, nlobsf));
        %let rc       = %sysfunc(close(&dsid));
    %end;
%mend;
%replace_missing_books;

student_book

Name	book
Alfred	book1
Alice	book2
Barbara	book9
Carol	book9
Henry	book3
James	book4
Jane	book8
Janet	book5

second_table

title	availability
book5	0
book6	2
book7	3
book8	3
book9	3

Normally a suggestion would be to use a SET statement with the POINT=<var> to select a random title to assign a student. However, due to the transactional nature, the availability should drop by 1 each time a book is assigned.

Example:

Assign each book available a random number (draw order) and use that in a 1:1 merge to assign to a student.

data books;
  call streaminit(20230410);

  do bookid = 1 to 30;
    avail = rand('integer',0,10);
    total + avail;
    output;
  end;

  put 'NOTE: total avail: ' total;
  drop total;
run;

data bookrandom;
  set books;
  do _n_ = 1 to avail;
    draw = rand('uniform');
    output;
  end;
run;

proc sort data=bookrandom;
  by draw;
run;

data students;
  do studentid = 1 to 100;
    output;
  end;
run;

data 
  studentbooks(keep=studentid bookid) 
  bookout(keep=bookid where=(not missing(bookid)))
;
  * 1-1 merge to assign an available book as previously random ordered;
  merge students bookrandom(drop=draw);

  if missing(studentid) then stop;
run;

data books;
  modify books bookout; 
  by bookid;
  avail = avail-1;  
run;

%let syslast=books;