Recursive Joins using proc sql

[SAS09]

Hoping someone can help me with the following problem:

I have dataset that contains the SSNs of sales agents who are linked to other agents. For example the dataset looks like this:

VAR1 VAR2 VAR3 VAR4 VAR5
ssn1 ssn3 ssn5 ssn6 ssn20
ssn2 ssn3 ssn5
ssn3 ssn1 ssn1 ssn5 ssn20
ssn4
ssn5 ssn3 ssn1 ssn6 ssn20

and so on.

I need to create flags that uniquely identify the distinct combinations. I am stumped and not sure where to begin - does anyone have any thoughts on how this problem can be addresses. Any thoughts would be greatly appreciated!

[sbb]

To the OP: please explain "I need to create flags that uniquely identify the distinct combination."

You have demonstrated the input side, now demonstrate what you would want to see on the output side (in a report, data-file, or whatever your rqmt).

Scott Barry
SBBWorks, Inc.

[SAS09]

Thank you for your note - what I really need are linkages between all the individual SSNs. For example if ssn1 is linked to SSN2 and SSN1 is also linked to SSN20 then I need to create a field that links SSN2 to SSN20 as well. I hope this clarifies.

[sbb]

Again if you can explain it visually, it is likely possible. Using references such as "I I need to create a field that links SSN2 to SSN2" doesn't help. Demonstrate it in a post-reply visually for effect (including naming the variable providing the "linkage" -- also, it may help you figure out the problem along the way.

Scott Barry
SBBWorks, Inc.

[SAS09]

Thanks - this is helping me refine what I want. I am not sure I can adequately display the final product - but let me try. Ultimately I need to pair every single SSN with every other SSN that it already has a direct relationship and also with those SSNs that it has an indirect relationship. So the final report needs to look like this:

Var1 Var2
SSN1 SSN2
SSN1 SSN3
SSN1 SSN5
SSN1 SSN9

In the above example SSN1 may only be directly related to SSN2-SSN5 but because SSN5 also has a relationship with SSN9, SSN1 needs to be linked to SSN9.

[sbb]

Using a DATA step approach, declare a SAS ARRAY with the "variable prefix name" you want to include in your array variable list, such as VAR: in the ARRAY statement. Then using a DO / END code paragraph loop, create two new variables NVAR1 and NVAR2 starting referencing each of the variables in the array and outputting when the two variables are not equal (presumably in your yet-to-be-stated-clearly rqmts).

The using PROC SORT NODUPKEY, remove the duplicate combinations of NVAR1 and NVAR2.

The resulting output files should provide all combinations.

Another twist on this approach would be to reduce your input file to two SAS variables to start, generating observations, again in a DO/END loop. Then use PROC SQL to create an "all possible combinations" as can be identified with the PROC SQL DOC - found with Google advanced search argument below.

A most formidable SAS learning experience, I would say -- of course there are others on the SAS Discussion Forums listening channel who would rather do the coding task for you - I prefer to guide, rather than pull someone along into learning the SAS system.

Suggest you get phase 1 under your belt, by creating a new SAS file with two variables (generated from your VAR1-VAR5 file), then investigate using PROC SQL.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic/post:

all possible combinations multiple variables site:sas.com

-- a resulting link from the above search results to consider in your quest:

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473718.htm

[SAS09]

Thank you Scott! This is very helpful.