topic Re: Keeping variables in %let macro in select statement in PROC SQL? in SAS Programming

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

Kjeldsen89 — Thu, 05 Nov 2020 12:21:15 GMT

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

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

PaigeMiller — Thu, 05 Nov 2020 12:30:14 GMT

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.

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

PaigeMiller — Thu, 05 Nov 2020 12:35:18 GMT

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

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

Kurt_Bremser — Thu, 05 Nov 2020 13:01:22 GMT

%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.