topic Re: Force set statement to first observation in SAS Programming

Force set statement to first observation

Paul_de_Barros — Fri, 22 Jan 2021 13:10:09 GMT

I would like to take a data set with 1 column containing distinct entries and expand it by making pairs of distinct entries. I thought i could do this by placing a second SET statement referencing the same data set inside a DO WHILE loop, but it's not working. The issue is that the buffer (or whatever) for the second SET statement appears to be preserved over iterations of the implicit loop of the DATA step. Is there a way to force a SET statement to read from the first observation, regardless of the iteration of the DATA step? My code is below with lots of PUT statements to show what's happening in the log:

data JustIndex;
	input ID @@;
	datalines;
1 2 3 4 5 6 7 8
;
run;

data pairs;
	set JustIndex;
	innerIndex = 1;
	put 'Before loop: ' _all_;
	Do while (innerIndex < _N_);
		put 'looping ' innerIndex;
		set JustIndex;
		ID2 = ID;
		output;
		innerIndex = innerIndex  + 1;
	end;
	put 'After loop:  ' _all_;
run;

And here's the log for the second DATA step:

Before loop: ID=1 innerIndex=1 ID2=. _ERROR_=0 _N_=1
After loop: ID=1 innerIndex=1 ID2=. _ERROR_=0 _N_=1
Before loop: ID=2 innerIndex=1 ID2=. _ERROR_=0 _N_=2
looping 1
After loop: ID=1 innerIndex=2 ID2=1 _ERROR_=0 _N_=2
Before loop: ID=3 innerIndex=1 ID2=. _ERROR_=0 _N_=3
looping 1
looping 2
After loop: ID=3 innerIndex=3 ID2=3 _ERROR_=0 _N_=3
Before loop: ID=4 innerIndex=1 ID2=. _ERROR_=0 _N_=4
looping 1
looping 2
looping 3
After loop: ID=6 innerIndex=4 ID2=6 _ERROR_=0 _N_=4
Before loop: ID=5 innerIndex=1 ID2=. _ERROR_=0 _N_=5
looping 1
looping 2
looping 3

As you can see, the DO WHILE loop runs exactly 9 times. On the 9th time, the inner SET statement hits the end of the data set, causing the DATA step to terminate. The point of the innerIndex < _N_ condition was to prevent the inner SET statement from ever hitting the end of the data set.

Re: Force set statement to first observation

PeterClemmensen — Fri, 22 Jan 2021 13:48:55 GMT

Can you post your desired result? Is what you're after a cartesian product?

If so:

data JustIndex;
	input ID @@;
	datalines;
1 2 3 4 5 6 7 8
;
run;

data want;
   set JustIndex;
   do i=1 to n;
      set JustIndex(rename = ID = ID2) point = i nobs = n;
      output;
   end;
run;

Result:

Re: Force set statement to first observation

FreelanceReinh — Fri, 22 Jan 2021 13:39:48 GMT

Hello @Paul_de_Barros,

You may want to use the POINT= option of the SET statement:

Example (producing 8*8=64 pairs):

data pairs;
set JustIndex nobs=_n;
do _i=1 to _n;
  set JustIndex(rename=(ID=ID2)) point=_i;
  output;
end;
run;

By changing the DO statement to

do _i=_n_+1 to _n;

you can limit the output to the comb(8,2)=28 pairs (ID=i, ID2=j) with i<j.

Re: Force set statement to first observation

Paul_de_Barros — Fri, 22 Jan 2021 14:40:04 GMT

Thank you, @FreelanceReinh and @PeterClemmensen . The POINT= option was exactly what I was looking for.

Sorry if I wasn't clear about the desired output; I wasn't trying to get the full Cartesian product, just the unique unordered combinations.

My resulting solution was a little different from what you both suggested. In case you are interested, here's what I did:

data pairs;
	set JustIndex nobs=_n;
	Do _i=_N_+1 to _n;
		set JustIndex(rename=(ID=ID2)) point=_i;
		output;
	end;
run;