topic Re: How do I link together all chained pairs in SAS Data Management

How do I link together all chained pairs

newnoise — Tue, 21 May 2019 20:24:33 GMT

I've got a data set that looks something like this:

data pair_data;
	infile datalines missover;
	input var_id $ parent_var_id $;
	datalines;
		1	
		2	1
		3	2
		4	20
		20	
		21	4
		15	3
	;
run;

Ideally, I want to end up with a data set that combines all nested combinations, including the original ID (which has a blank parent_var value). So, it should look something like this:

data combined_pair_data;
	infile datalines missover;
	input combo_id $ var_id $ parent_var_id $;
	datalines;
		1	1	1
		1	2	1
		1	3	2
		2	4	20
		2	20	20
		2	21	4
		1	15	3
	;
run;

Notice that any original ID has its own ID filled in as the parent, but all linked combinations are given a sequential combo_id to differentiate chains of pairs.

I've used do loops up to this point, but that seems inefficient. I imagine there's gotta be a simple way to do this, but I can't put my finger on it. Any help would be greatly appreciated!

Looked elsewhere in the forums and didn't see anything like this.

Re: How do I link together all chained pairs

Reeza — Tue, 21 May 2019 20:27:40 GMT

This isn't a trivial task.

But it's been asked many times.

Here's a recent answer that's relevant and pretty much what you're trying to do:

https://communities.sas.com/t5/SAS-Data-Management/Recursive-Query/m-p/466861#M14584

@newnoise wrote:

I've got a data set that looks something like this:
data pair_data;
	infile datalines missover;
	input var_id $ parent_var_id $;
	datalines;
		1	
		2	1
		3	2
		4	20
		20	
		21	4
		15	3
	;
run;
Ideally, I want to end up with a data set that combines all nested combinations, including the original ID (which has a blank parent_var value). So, it should look something like this:
data combined_pair_data;
	infile datalines missover;
	input combo_id $ var_id $ parent_var_id $;
	datalines;
		1	1	1
		1	2	1
		1	3	2
		2	4	20
		2	20	20
		2	21	4
		1	15	3
	;
run;
Notice that any original ID has its own ID filled in as the parent, but all linked combinations are given a sequential combo_id to differentiate chains of pairs.

I've used do loops up to this point, but that seems inefficient. I imagine there's gotta be a simple way to do this, but I can't put my finger on it. Any help would be greatly appreciated!

Looked elsewhere in the forums and didn't see anything like this.

Re: How do I link together all chained pairs

newnoise — Tue, 21 May 2019 20:31:55 GMT

Ah, my apologies! I hadn't seen the other post(s) and wasn't familiar with the 'recursive' descriptor for this situation. Thanks for pointing me in direction of an answer!