SAS Newbie: How do I use a basic macro variable in proc SQL?

[interlockjohn]

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?

[Kurt_Bremser]

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

[Patrick]

@Kurt_Bremser 

The RUN statement in the macro which gets called within the Proc SQL is certainly not a good thing.

[Kurt_Bremser · Accepted solution]

---

@Patrick wrote:

@Kurt_Bremser 

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.

[interlockjohn]

Ah I got it to work after restarting SAS. Thanks for the help Kurt!

[interlockjohn]

Thanks for your reply Kent. I still have no output when trying your solution. The log output is just a copy of the code I posted.

[Kurt_Bremser]

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         

[Patrick]

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;

[interlockjohn]

Thanks for your reply Patrick. This was my first thought, but I am trying to learn this for a school assignment.

The assignment is basically a bunch of proc SQL queries, and I wanted to avoid copy and pasting the code you pasted above into each query. I wanted to be able to just call the variable on each new problem.