Looking to loop through input from a SAS data table to query a SQL database

narnia649 — Tue, 01 Mar 2022 14:15:57 GMT

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();

Re: Looking to loop through input from a SAS data table to query a SQL database

PaigeMiller — Tue, 01 Mar 2022 14:22:30 GMT

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;

topic Re: Looking to loop through input from a SAS data table to query a SQL database in SAS Programming

Looking to loop through input from a SAS data table to query a SQL database

Re: Looking to loop through input from a SAS data table to query a SQL database