selecting and removing observations from two data sets iteratively

[Reeza]

That will most likely need a macro.

It looks like you have a single iteration built, so I assume you're not familiar with macro coding and want someone to write the macro for you?

[SWEETSAS]

Yes. I am having a hard time writing the macro that can do this iteratively.

[Tom · Accepted solution]

Try this macro.

%macro match

(cases=ref       /* List of records to use as CASES */

,controls=main   /* List of records to use as CONTROLS */

,id=id           /* ID variable in CONTROLS */

,varlist=x y     /* Variables to use in distance calculations */

,out=out         /* Output dataset */

,with_replacement=0  /* Select with replacement? (0/1) */

);

%local i n rename remove ;

%* Generate RENAME list for &VARLIST varaibles ;

%do i=1 %to %sysfunc(countw(&varlist));

  %let rename=&rename %scan(&varlist,&i)=match_%scan(&varlist,&i);

%end;

* Get count of number of cases ;

data _null_;

  if eof then call symputx('n',_n_ -1);

  set &cases end=eof;

run;

* Use all CONTROL records to start with ;

data distance;

  set &controls;

  keep &id &varlist ;

run;

%if %sysfunc(exist(&out)) or %sysfunc(exist(&out,view)) %then %do;

* Remove existing output dataset ;

proc delete data=&out; run;

%end;

%* Set REMOVE to missing value to that all controls used on first pass;

%let remove=.;

%do i=1 %to &n ;

* Calculate distance from next CASES, exclude previosly used CONTROL ;

proc fastclus maxc=1 replace=none maxiter=0 noprint

  data=distance(where=((&id ^= &remove)))

  seed=&cases (firstobs=&i obs=&i )

  out=distance ;

;

  var &varlist;

run;

* Sort to find closest ;

proc sort; by distance ; run;

* Generate next record ;

* Set REMOVE to eliminate this case ;

data next ;

  set &cases (firstobs=&i obs=&i);

  set distance(obs=1 keep=&id &varlist distance rename=(&id=match_&id &rename));

  if not &with_replacement then call symputx('remove',match_&id);

run;

* Add to output dataset ;

proc append base=&out data=next force;

run;

%end;

%mend match;

data main;

input id x y;

datalines;

1 0.5 0.3

2 0.7  1.3

3 2.0  0.8

4 0.6 0.45

5 .25   .4

;

data ref;

input id x y;

datalines;

1 0.6  1.2

2 0.5  0.7

3 -0.3  1.5

;

options mprint ;

%match() ;

proc print;

run;

%match(with_replacement=1) ;

proc print;

run;

Without Replacement
Obs	id	x	y	match_id	match_x	match_y	DISTANCE
1	1	0.6	1	2	0.7	1.3	0.14142
2	2	0.5	1	4	0.6	0.45	0.26926
3	3	-0.3	2	5	0.25	0.4	1.22984
With Replacement
Obs	id	x	y	match_id	match_x	match_y	DISTANCE
1	1	0.6	1	2	0.7	1.3	0.14142
2	2	0.5	1	4	0.6	0.45	0.26926
3	3	-0.3	2	2	0.7	1.3	1.0198

[SWEETSAS]

Tom.

This is I-N-C-R-E-D-I-B-L-E!!!!!!!You made my Christmas! This a best Santa gift ever.