Keeping variables in %let macro in select statement in PROC SQL?

[Kjeldsen89]

Hello 

Hope there is someone who can help me with this one:

I have multiple datasets called BEF201212, BEF201312, BEF201412 and etc. I do also have en external datafile which I have to join on my BEF201212 etc. tables. Therefore I have chosen a do loop in a Proc SQL procedure. 

I want to make a %let variable where the variables I want to keep from BEF201212 etc. is located. So next time I need the code with different variables I can just change the %let macro statement. 

My code look like this:

%let BEF = B.PNR, B.FAMILIE_ID, B.OPR_LAND

%macro sql_bef(start,end);

%do year = &start. %to &end. 

proc sql;

create tables test as 

select A.ID , &BEF. 

on A.ID = B.ID

order by ID;

quit;

%end;

%mend;

%sql_bef(2012,2018)

When I run the above macrocode my log tells me: 

"NOTE: Line generated by the macro variable "BEF". 

B.PNR, B.FAMILIE_ID, B.OPR_LAND GOPTIONS NOACCESIBLE 

How can I define my select variables in a macro? 

Kind regards Frank 

[PaigeMiller]

Use the command

options mprint;

at the start of your program and run it again. Show us the LOG for this execution of the macro named %SQL_BEF. Please follow these instructions exactly. Copy the log as text and paste it here into the window that appears when you click on the </> icon. DO NOT SKIP THIS STEP. Also, show us the complete log with nothing chopped out for this entire macro execution portion of your program.

[PaigeMiller]

Also, the code you present is missing a semi-colon, make sure you fix that.

[Kurt_Bremser]

%let BEF = B.PNR, B.FAMILIE_ID, B.OPR_LAND /* missing semicolon */

%macro sql_bef(start,end);
%do year = &start. %to &end. /* missing semicolon */
proc sql;
create tables test as 
select A.ID , &BEF. 
on A.ID = B.ID /* missing from and join clause */
order by ID;
quit;
%end;
%mend;
%sql_bef(2012,2018)

Given the multiple serious syntax mistakes in this code piece, I STRONGLY suggest you start with non-macro code first and get it to run without ANY ERRORs, WARNINGs or extraneous NOTEs. Only when that goal has been reached, you can start to make the code dynamic.