Hello all,
I am having trouble figuring out how to use macro variables in proc sql.
So I have the following:
%let fullName = John Smith;
%macro JohnSmith;
title "Completed by &fullName on &sysday, &sysdate, &systime";
run;
%mend;
I am trying to use it in the context of proc sql. Here is an example.
/*Counting rows*/
proc sql;
select count(*) as NumOfRows
from Tmp1.myData;
quit;
My guess would to write it like this, but I get no output.
/*Counting rows*/
proc sql;
%JohnSmith
select count(*) as NumOfRows
from Tmp1.myData;
quit;
Which part of my code is incorrect?
@Patrick wrote:
The RUN statement in the macro which gets called within the Proc SQL is certainly not a good thing.
Well, it's not necessary, but it only causes a NOTE:
37 %let fullName = John Smith; 38 %macro JohnSmith; 39 title "Completed by &fullName on &sysday, &sysdate, &systime"; 40 run; 41 %mend; 42 proc sql; 43 %JohnSmith NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. 44 select count(*) as NumOfRows 45 from sashelp.class; 46 quit; NOTE: The PROCEDURE SQL printed page 1. NOTE: PROZEDUR SQL used (Total process time): real time 0.01 seconds cpu time 0.00 seconds
I just wanted to show that the code as posted causes no WARNING/ERROR and produces the desired output, as long as the library is assigned and the dataset exists.
Note to @interlockjohn: the macro preprocessor is no function generator, it inserts code where a macro trigger is called, so you need to have an eye on what code will be created by your call. This also means that a THOROUGH understanding of the SAS language is an absolute requirement for creating valid macro code.
Welcome to the Communities!
I see nothing wrong with your code, as long as I provide a dataset for input:
%let fullName = John Smith;
%macro JohnSmith;
title "Completed by &fullName on &sysday, &sysdate, &systime";
run;
%mend;
proc sql;
%JohnSmith
select count(*) as NumOfRows
from sashelp.class;
quit;
as I do not have library tmp1, for obvious reasons. My listing output is
Completed by John Smith on Tuesday, 10DEC19, 07:45 07:45 Tuesday, December 10, 2019 1 NumOfRows --------- 19
Please post your log (use the {i} button, please).
The RUN statement in the macro which gets called within the Proc SQL is certainly not a good thing.
@Patrick wrote:
The RUN statement in the macro which gets called within the Proc SQL is certainly not a good thing.
Well, it's not necessary, but it only causes a NOTE:
37 %let fullName = John Smith; 38 %macro JohnSmith; 39 title "Completed by &fullName on &sysday, &sysdate, &systime"; 40 run; 41 %mend; 42 proc sql; 43 %JohnSmith NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. 44 select count(*) as NumOfRows 45 from sashelp.class; 46 quit; NOTE: The PROCEDURE SQL printed page 1. NOTE: PROZEDUR SQL used (Total process time): real time 0.01 seconds cpu time 0.00 seconds
I just wanted to show that the code as posted causes no WARNING/ERROR and produces the desired output, as long as the library is assigned and the dataset exists.
Note to @interlockjohn: the macro preprocessor is no function generator, it inserts code where a macro trigger is called, so you need to have an eye on what code will be created by your call. This also means that a THOROUGH understanding of the SAS language is an absolute requirement for creating valid macro code.
Ah I got it to work after restarting SAS. Thanks for the help Kurt!
The log is NEVER just a "copy of the code". It contains line numbers and messages and is ESSENTIAL in understanding what happens (see Maxim 2). NEVER dismiss it out of hand. In your specific case, please post the complete (top to bottom!) log that is created when you run this (and only this!) code:
%let fullName = John Smith;
%macro JohnSmith;
title "Completed by &fullName on &sysday, &sysdate, &systime";
run;
%mend;
proc sql;
%JohnSmith
select count(*) as NumOfRows
from Tmp1.myData;
quit;
like this:
1 Das SAS System 07:45 Tuesday, December 10, 2019 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='Program (3)'; 4 %LET _CLIENTPROCESSFLOWNAME='Process Flow'; 5 %LET _CLIENTPROJECTPATH=''; 6 %LET _CLIENTPROJECTPATHHOST=''; 7 %LET _CLIENTPROJECTNAME=''; 8 %LET _SASPROGRAMFILE=''; 9 %LET _SASPROGRAMFILEHOST=''; 10 11 ODS _ALL_ CLOSE; 12 OPTIONS DEV=ACTIVEX; 13 GOPTIONS XPIXELS=0 YPIXELS=0; 14 ODS LISTING GPATH=&sasworklocation; 15 FILENAME EGHTML TEMP; 16 ODS HTML(ID=EGHTML) FILE=EGHTML 17 ENCODING='utf-8' 18 STYLE=Default 19 STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/Default.css") 20 ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v94/sasgraph.exe#version=9,4") 21 NOGTITLE 22 NOGFOOTNOTE 23 GPATH=&sasworklocation 24 ; NOTE: Writing HTML(EGHTML) Body file: EGHTML 25 FILENAME EGSR TEMP; 26 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 27 STYLE=Default 28 STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/Default.css") 29 NOGTITLE 30 NOGFOOTNOTE 31 GPATH=&sasworklocation 32 ENCODING=UTF8 33 options(rolap="on") 34 ; NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 35 36 GOPTIONS ACCESSIBLE; 37 %let fullName = John Smith; 38 %macro JohnSmith; 39 title "Completed by &fullName on &sysday, &sysdate, &systime"; 40 run; 41 %mend; 42 proc sql; 43 %JohnSmith NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. 44 select count(*) as NumOfRows 45 from sashelp.class; 46 quit; NOTE: The PROCEDURE SQL printed page 1. NOTE: PROZEDUR SQL used (Total process time): real time 0.01 seconds cpu time 0.00 seconds 47 48 GOPTIONS NOACCESSIBLE; 49 %LET _CLIENTTASKLABEL=; 2 Das SAS System 07:45 Tuesday, December 10, 2019 50 %LET _CLIENTPROCESSFLOWNAME=; 51 %LET _CLIENTPROJECTPATH=; 52 %LET _CLIENTPROJECTPATHHOST=; 53 %LET _CLIENTPROJECTNAME=; 54 %LET _SASPROGRAMFILE=; 55 %LET _SASPROGRAMFILEHOST=; 56 57 ;*';*";*/;quit;run; 58 ODS _ALL_ CLOSE; 59 60 61 QUIT; RUN; 62
If being rather new to SAS coding: Stay away from SAS macro coding and get first more familiar with Base SAS code. Too many people start too early with SAS Macro coding, get confused and end up creating a big mess.
Having said that: Just using a SAS Macro variable in your code (NOT defining as SAS Macro) is o.k. With your code that could look like below.
%let fullName = John Smith;
title "Completed by &fullName on &sysday, &sysdate, &systime";
proc sql;
select count(*) as NumOfRows
from Tmp1.myData
;
quit;
title;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.