Error using macro variable in table column name in inner join

[ha33]

Hello experts,

I am fairly new to SAS.

I'm trying to use an inner join to match unique identifiers in two tables. I have made a macro variable in which I can change the year, drug code and name I am interested in. I get an error and it seems to be because there is a macro variable in the table name. I am stuck as to figuring out a workaround.

The code is as follows: 

%macro prevusers(year, code, substance) ;

proc sql;

       create table file1.a&year&substance as

                   select distinct ID1, ID2 from file1.pop1 inner join file1.subs_list&year

                               on pop1.ID1=subs_list&year.ID2
                                        where code like "&code";

 %mend prevusers;

%prevusers (1995, J01%, antibio)

%prevusers (1996, J01%, antibio)

%prevusers (1997, J01%, antibio)

The error states: "The following columns were not found in the contributing tables: subs_list1995ID2."

Apparently it interprets subs_list&year.ID2 as a column name and ignores the dot separating the table name and column name. Any ideas as to how this issue can be resolved?

[Quentin · Accepted solution]

Hi,

You need another dot:

subs_list&year..ID2

In the macro language if you do:

%let foo=foo;
%put &foobar;

The macro language will look for macro variable named foobar, which doesn't exist.  You use a dot to tell the macro processor that the name of the macro variable has ended.  So if you want a dot in your code, you need two dots, .e.g.:

1    %let foo=foo;
2    %put &foobar;
WARNING: Apparent symbolic reference FOOBAR not resolved.
&foobar
3    %put &foo.bar ;
foobar
4    %put &foo..bar ;
foo.bar

[ha33]

Thanks a lot! Works perfectly now.