how to do 1 to N matching in sas

Reply
New Contributor
Posts: 3

how to do 1 to N matching in sas

[ Edited ]

Gday,

 

I would like to select unique matching pair from dataset. I post one dataset as example. 

 

DATA Have;
INFILE Cards;
INPUT X Y $ @@;
CARDS;
1 A 2 A 3 A 4 A 5 B 6 B 7 B 8 B 1 B 2 B 3 B 4 B ;
RUN;

 

In dataset Have, x=1 can match with y=A or Y=B, as well as x=2. My goal is to select unique matching pair. I need to complete several steps: first, I select the pair of 1 and A and output to a new dataset,

         second, I need to exclude the pair having either 1 or A for the rest of dataset and update the orignial dataset

         then, after exlusion, I process to select the second pair in the update dataset, which should be the pair of 2 and B in this case.

Ideally, the final dataset I need will include two pairs, 1 and A, 2and B. I tried different way, such DOW do-loop, link statement or sql subqury. All of them failed. 

 

I am open for any suggestions for this complex question. 

 

Thanks

Lingshu

Frequent Contributor
Posts: 95

Re: how to do 1 to N matching in sas

Hi Lingshu,

 

Welcome to the forum!

 

Interesting problem. How do you define "matching"? Is there one rule, or several rules with an order of preference?

Norman.
SAS 9.4 (TS1M4) X64_7PRO WIN 6.1.7601 Service Pack 1 Workstation

Super User
Posts: 7,762

Re: how to do 1 to N matching in sas

This at least solves your test case:

data have;
input x y $;
cards;
1 A
2 A
3 A
4 A
5 B
6 B
7 B
8 B
1 B
2 B
3 B
4 B
;
run;

proc sort data=have;
by x y;
run;

%macro iterate;
%let done=no;
%let start = 1;
%do %until ("&done" = "yes");

data have (drop=x1 y1);
set have;
retain x1 y1;
if _n_ < &start then call symput('done','yes');
else if _n_ = &start
then do;
  x1 = x;
  y1 = y;
  call symput('done','yes');
end;
else do;
if x = x1 or y = y1
then do;
  call symput('done','no');
  delete;
end;
end;
run;
%let start=%eval(&start+1);
%end;
%mend;
%iterate;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 95

Re: how to do 1 to N matching in sas

Posted in reply to KurtBremser

Hi

 

Norman.
SAS 9.4 (TS1M4) X64_7PRO WIN 6.1.7601 Service Pack 1 Workstation

Super User
Posts: 7,762

Re: how to do 1 to N matching in sas


Norman21 wrote:

Hi

 


I don't have the slightest idea, but it is the solution the OP wanted.

Maybe further discussion will lead to a clarification of the selection rules.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 3

Re: how to do 1 to N matching in sas

Posted in reply to KurtBremser

Hi Norman21,

 

Thanks for asking. 

My study has 300 hundreds cases and 1000 controls. I need to match 1 case with 3 controls. I did full matching first, and then figured out 1 case can matched with 500 controls (having variable called scarce to calculate the number of matched control for each case) and 1 control can can at least 2 cases (having variable called demand to calculate the number of matched case for each control). My strategy is to sort case and control by "scarce" and "demand". Pair with lowest scarce and demand scores will be selected first.

 

Lingshu

Frequent Contributor
Posts: 95

Re: how to do 1 to N matching in sas

[ Edited ]

Hi Lingshu,

 

Thanks for the additional information, but I'm still not sure what are your matching criteria.

 

Do the examples in the following describe that you are trying to achieve?

 

http://www2.sas.com/proceedings/sugi29/173-29.pdf

Norman.
SAS 9.4 (TS1M4) X64_7PRO WIN 6.1.7601 Service Pack 1 Workstation

New Contributor
Posts: 3

Re: how to do 1 to N matching in sas

[ Edited ]
Posted in reply to KurtBremser

Thanks so much KurtBremser,

 

That's awesome. I have an advanced question. The example I gave is to select only one pair for x=1, but if I need to select two pairs for x=1, keeping other selection rules same. In other word, I may need pairs of "1, A" "1, B" "2, C" "2, D".  I tried to use array to create y1-y3 to delete, but it seems not work. Do you have any idea?

Attached is my code.

 

data have;
input x y $;
cards;
1 A
2 A
3 A
4 A
5 B
6 B
7 B
8 B
1 B
2 B
3 B
4 B
5 C
6 C
7 C
8 C
1 D
2 C
3 D
4 D
2 D
;
run;

proc sort data=have;
by x y;
run;

%macro iterate;
%let done=no;
%let start = 1;
%do %until ("&done" = "yes");

data have (drop=x1 y1 y2);
set have;
array a{2} y1-y2;
retain x1 y1;
if _n_ < &start then call symput('done','yes');
else if &start=<_n_ < &start+2
then do;
x1 = x;
do i=1 to 2;
if _n_ =&start-1+i then a{i}=control_id;
else a{i}=0;
end;
call symput('done','yes');
end;
else do;
if x = x1 or y = y1 or y=y2
then do;
call symput('done','no');
delete;
end;
end;
run;
%let start=%eval(&start+2);
%end;
%mend;
%iterate;

Ask a Question
Discussion stats
  • 7 replies
  • 505 views
  • 1 like
  • 3 in conversation