Hi, I am trying to build something that combines all values of all variables using SQL within SAS. For Example: A B C D A1 B1 C1 D1 A2 B2 C2 A3 C3 A4 C4 C5 A1 B1 C1 D1 A1 B1 A1 B2 A1 B1 C1 D1 and so on.. Basically making all possible filter combinations on variables. I have been a little bit succesful but the problem is with storage because the combinations can be so many. I wanted your help in trying to make a SAS procedure to make all these filter combinations and also check performance of each combination on a criteria. But more importantly I am trying to understand how to make the combinations with out killing my storage which is very limited. This is my code:
DECLARE @i INT SET @i = 0 WHILE (@i < 15) BEGIN SET @i = @i + 1 EXEC ('CREATE TABLE VAR' + @i + '(VAR' + @i + ' VARCHAR(50))') END
INSERT INTO VAR1 (VAR1) SELECT DISTINCT VAR1 FROM [Training Data]
DECLARE @i INT SET @i = 0 WHILE (@i < 15) BEGIN SET @i = @i + 1 EXEC('INSERT INTO VAR' + @i + ' (VAR' + @i + ')' + ' SELECT DISTINCT VAR' + @i + ' FROM [Training Data]') END
CREATE TABLE ALL_COMB (VAR1 VARCHAR(50), VAR2 VARCHAR(50), VAR3 VARCHAR(50), VAR4 VARCHAR(50), VAR5 VARCHAR(50), VAR6 VARCHAR(50), VAR7 VARCHAR(50), VAR8 VARCHAR(50), VAR9 VARCHAR(50), VAR10 VARCHAR(50), VAR11 VARCHAR(50), VAR12 VARCHAR(50), VAR13 VARCHAR(50), VAR14 VARCHAR(50), VAR15 VARCHAR(50)) INSERT INTO ALL_COMB (VAR1, VAR2, VAR3, VAR4, VAR5, VAR6, VAR7, VAR8, VAR9, VAR10, VAR11, VAR12, VAR13, VAR14, VAR15) SELECT * FROM VAR1, VAR2, VAR3, VAR4, VAR5, VAR6, VAR7, VAR8, VAR9, VAR10, VAR11, VAR12, VAR13, VAR14, VAR15 ORDER BY VAR1, VAR2, VAR3, VAR4, VAR5, VAR6, VAR7, VAR8, VAR9, VAR10, VAR11, VAR12, VAR13, VAR14, VAR15;
TRUNCATE TABLE VAR1
select sum(Outcome)/count(Outcome) as Perc, count(Outcome) as Lines from [Training Data] where var1 = 1
SELECT * FROM ALL_COMB
... View more
