topic Re: Do loop error, unintended record show up in SAS Programming

Do loop error, unintended record show up

hhchenfx — Fri, 31 Mar 2017 20:43:10 GMT

Hello,

I want to make a combination of 2 files, 1 record of this file and 1 record of other file.

So with 3 records in 1 file and 2 records in other file, I should have 6 combinations.

I write the usual code and suddendy there are few unintended records created in final file (the last 2 record in file want)

The code is so straightforward but I cant find the cause.

Could anyone please have a look and help me fix it?

Many thanks.

HHC

data have_var1; 
input a_name $ a_value b_name $ b_value;
datalines;
a 4 b 5
a 4 c 9
b 5 c 9

;run;


data have_var2; 
input c_name $ c_value;
datalines;
c 90
d 100
;run;

	*Create combination of any 2 row from 2 file;
		data want;									
		set have_var1 nobs=totalobs1;
		drop i ;
		i+1;
		set have_var2 nobs=totalobs2;
		drop k ;
		k+1;

		do j=i to totalobs1 ;
		set have_var1(keep = a_name a_value b_name b_value) point=j;

		do l=k to totalobs2 ;
		set have_var2(keep = c_name c_value) point=l;
		
			/*if a_name^=c_name and b_name^=c_name then*/

			output;
			end;

		end;
		run;

Re: Do loop error, unintended record show up

Tom — Fri, 31 Mar 2017 20:55:44 GMT

You are making it way too complicated.

data want;									
do j=1 to totalobs1 ;
  set have_var1(keep = a_name a_value b_name b_value) point=j nobs=totalobs1;
  do l=1 to totalobs2 ;
    set have_var2(keep = c_name c_value) point=l nobs=totalobs2;
    output;
  end;
end;
stop;
run;

Make sure to add a STOP to prevent it from running for ever. Remember that normal data steps stop when they read past the end of the input files. When using POINT= option it will never read past the end of the input.

Re: Do loop error, unintended record show up

art297 — Fri, 31 Mar 2017 21:08:59 GMT

You can simplify it even more:

data want (drop=i);
  set have_var1;
  do i=1 to nobs;
    set have_var2  nobs=nobs point=i;
    output;
  end;
run;

Art, CEO, AnalystFinder.com

Re: Do loop error, unintended record show up

Kurt_Bremser — Fri, 31 Mar 2017 21:09:07 GMT

Can't try it from where I am at the moment (hotel room in Venice), but try a SQL cartesian join:

proc sql;
create table want as
select *
from have_var1, have_var2
;
quit;

Re: Do loop error, unintended record show up

hhchenfx — Fri, 31 Mar 2017 21:16:44 GMT

Oh that STOP!
my code really look ... 🙂
Thank you.
HHC