BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
newnoise
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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.


 

 

View solution in original post

2 REPLIES 2
Reeza
Super User

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.


 

 

newnoise
Fluorite | Level 6
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!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 661 views
  • 4 likes
  • 2 in conversation