Creating combinations from multiple columns in data set in SAS

0
votes

This relates to one of my previous questions (SAS - Creating combinations of different independant variables with lags). The HAVE matrix looks like below. Is there an easy way in SAS to create combinations from variables appearing in the multiple columns? E.g. columns headings Var1, Var2, Var3 - Var1 Var2 Var3

VarA VarB VarC

VarA1 VarB1 VarC1

..

VarA4 VarB4 VarC4

The combinations would be either - a) selecting any two columns e.g. Var1 and Var2 and then selecting one element from each column e.g. one combination could be VarA1 VarB2 b) selecting all columns and one element from each. I want to have all such possible combinations in a) and b). I searched through for this including proc plan but couldn't get much. Is there is proc to do all such combinations for multiple columns?

PS: VarA, VarA1, ..VarB, VarB1,..all of them are just text/strings.

Thanks

To add, I saw through http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#n082a03omu3i21n1k889zfklh4ps.htm which could work for 2 columns, but would get cumbersome with multiple columns..

! [EDIT] WANT matrix could be something like below (considering only two lags ..)

VarA VarB
VarA VarB1
VarA VarB2
VarA1 VarB
VarA1 VarB1
VarA1 VarB2
… …
VarB VarC
VarB VarC1
VarB VarC2
VarB1 VarC
VarB1 VarC1
VarB1 VarC2
… …
VarA VarC
VarA VarC1
VarA VarC2
… …

VarA VarB VarC
VarA VarB VarC1
VarA VarB VarC2
VarA VarB1 VarC
VarA VarB1 VarC1
VarA VarB1 VarC2
VarA VarB2 VarC
VarA VarB2 VarC1
VarA VarB2 VarC2 … … …

1
sascombinations
Your question is not clear and I'm not sure what you are trying to do. Is this by any chance creating interaction variables for regression analysis? Anyway, you may want to provide a more concrete example output of what you want. – user1509107
Right, I want to design all combinations of input variables with different lags. Edited post to show output. – user2508567

1 Answers

1
votes

Seems like the best way to do this is to transpose your original dataset to one column, then join it to itself using a cartesian join (or any of the other methods mentioned in the paper you posted). As long as you want the combination of everything with everything else or some subset, the vertical transpose is very easy. Then just remove the combinations you don't want using WHERE; so for example if you want only cross-column combinations, your data looks like you could use SUBSTR to look at the 4th character ('A' 'B' 'C') and just remove any where they are equal.