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?
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
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.