I have test_input which is table that I would like to loop through and query those combinations of variables x1 & x2 (("A", "A"), ("A", "B"), etc...).
What's the easiest and most effective way to do so?
Note, example code below shows a manual attempt. Then, I did an automatic approach where I create a concatenated column of multiple variables, create macro variable list, then loop through that list using scan(). It seems very complicated and a lot of code. Is there a better way?
Code:
DATA test_input; INPUT x1 $ x2 $; DATALINES; A A A B A C B A B B B C ; RUN; DATA data_table; INPUT x1 $ x2 $ x3; DATALINES; A A 1 A A 2 A B 3 A B 4 A B 5 A B 6 B A 7 B B 8 B B 9 B B 10 B C 11 B C 12 B C 13 ; RUN; /* Manual */ %PUT _ALL_; %LET x1 = "A"; %LET x2 = "B"; PROC SQL; CREATE TABLE queried_data AS SELECT x1, x2, x3, compress(catx(x1, "_", x2)) AS concat_condition FROM data_table WHERE x1 = &x1 AND x2 = &x2 ; QUIT; /* Automatic */ DATA test_input; SET test_input; concat_condition = compress(catx(x1, "_", x2)); RUN; PROC SQL noprint; SELECT quote(trim(concat_condition)) INTO :concat_condition SEPARATED BY "~" FROM test_input ; QUIT; %MACRO loop_condition(); %DO i=1 %TO 5; %LET cur_var = %scan(&concat_condition, &i, "~"); PROC SQL; CREATE TABLE queried_data_&i AS SELECT x1, x2, x3, compress(catx(x1, "_", x2)) AS concat_condition FROM data_table WHERE CALCULATED concat_condition = "&cur_var" ; QUIT; %END; %MEND; %loop_condition();
Well, possibly I don't understand the entire task, but I don't think you need loops, and I don't think you need macros.
Something like this ought to work
proc sql;
create table final as select a.x1,a.x2,a.x3 from data_table a
left join test_input b where a.x1=b.x1 and a.x2=b.x2;
quit;
Well, possibly I don't understand the entire task, but I don't think you need loops, and I don't think you need macros.
Something like this ought to work
proc sql;
create table final as select a.x1,a.x2,a.x3 from data_table a
left join test_input b where a.x1=b.x1 and a.x2=b.x2;
quit;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.