topic Error using macro variable in table column name in inner join in SAS Programming

Error using macro variable in table column name in inner join

ha33 — Wed, 14 Dec 2022 16:01:39 GMT

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?

Re: Error using macro variable in table column name in inner join

Quentin — Wed, 14 Dec 2022 16:47:20 GMT

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

Re: Error using macro variable in table column name in inner join

ha33 — Thu, 15 Dec 2022 09:01:05 GMT

Thanks a lot! Works perfectly now.