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?
Yes. I am having a hard time writing the macro that can do this iteratively.
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 |
Tom.
This is I-N-C-R-E-D-I-B-L-E!!!!!!!You made my Christmas! This a best Santa gift ever.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.