SAS Proc SQL in macro

Question

I am testing my macro with below test dataset but a table with no rows is returned. Seems the SQL cannot recognize the condition in where clause. Please help to see what's wrong. Thanks a lot!

Test Dataset:

Index_1	Index_2
1	x_1
1	x_2
2	x_1
2	x_3

Code:

%macro looping;
%do i = 1 %to 1;
proc sql;
    Create table TEMP as
    select index_1, index_2, count(index_2) as Frequency
    from work.test
    where index_2 = 'x_&i.'
    group by index_1, index_2;

quit;
%end;
%mend;

No rows are returned because `create table` doesn't return rows. — Gordon Linoff, May 14 '21 at 10:19
Macro variables are not resolved when within single quotes. Use double quotes. `"x_&i."`. If you are attempting to get a cross tabulation showing which index_2's are associated with which index_1's there are far better ways. — Richard, May 14 '21 at 10:40

score 1 · Answer 1 · answered May 14 '21 at 16:58

You are probably getting zero observations because none of the values of INDEX_2 are the literal text x_&i.. If you use single quotes on the outside of string the macro processor ignores that string when looking for text with macro triggers. Use double quotes to make your string literal and the macro processor will process the reference to the macro variable I.

where index_2 = "x_&i."

Now you just need to worry if INDEX_2 contains the literal text x_1 instead.

SAS Proc SQL in macro

1 Answers1