Desktop productivity for business analysts and programmers

Macro around several proc sql

Reply
Contributor
Posts: 39

Macro around several proc sql

Hi all,

 

I am working with Guide, and I am doing something easy like a macro with several PROC SQL inside:

 

 

%macro A;

PROC SQL;
[...]
QUIT;
RUN;

PROC SQL;
[...]
QUIT;
RUN;

PROC SQL;
[...]
QUIT;
RUN;

PROC SQL;
[...]
QUIT;
RUN;

PROC SQL;
[...]
QUIT;
RUN;

[...]

%mend;
%A;

 

 

 

When I create a "program" task and I copy this and fill in the proc sql parts and run it, it is ok, it is working.

 

Now, I want to do the same thing, but having PROC SQL dispatched in several "Program" tasks, in order to keep the readability of the project process.

 

So I create a first "Program" task with only this:

 

 

%macro A;

 

 

I create as many "Program" task as the number of PROC SQL and I copy only one PROC SQL in each of them.

 

PROC SQL;
[...]
QUIT;
RUN;

I link all them to the first "Program" task

 

I finish with a last "Program" task writing only this:

 

%mend;
%A;

 

 

I link this last task to all PROC SQL "Program" tasks.

And I run the all. And doing this, it is not working,

 

I get an issue in my first PROC SQL "Program" task: (I replaced the field name and table name by [...] as it is not important and a bit secret)

 

 

1                                                          The SAS System                                12:25 Monday, June 19, 2017

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='SQLTASK1';
4          %LET _CLIENTPROJECTPATH='mypathproject';
5          %LET _CLIENTPROJECTNAME='myproject.egp';
6          %LET _SASPROGRAMFILE=;
7          
8          ODS _ALL_ CLOSE;
9          OPTIONS DEV=ACTIVEX;

           _________________________________
           49
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release.  Inserting white space 
             between a quoted string and the succeeding identifier is recommended.

10         GOPTIONS XPIXELS=0 YPIXELS=0;
11         FILENAME EGSR TEMP;
12         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
13             STYLE=HtmlBlue
14             STYLESHEET=(URL="file:///D:/APPS/SAS94/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
14             STYLESHEET=(URL="file:///D:/APPS/SAS94/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css"
                                                                                                _
                                                                                                49
14       ! )
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release.  Inserting white space 
             between a quoted string and the succeeding identifier is recommended.

15             NOGTITLE
16             NOGFOOTNOTE
17             GPATH=&sasworklocation
18             ENCODING=UTF8
19             options(rolap="on")
20         ;
21         
22         GOPTIONS ACCESSIBLE;
23         PROC SQL;
24            CREATE TABLE WORK.A AS
25            SELECT [...]
26                   
27                  
28                   
29                   
30                 
31                  
32                  
WARNING: The quoted string currently being processed has become more than 262 characters long.  You might have unbalanced quotation 
         marks.
33               FROM [...];
34         QUIT;
35         RUN;
36         
37         
38         
39         GOPTIONS NOACCESSIBLE;
40         %LET _CLIENTTASKLABEL=;
41         %LET _CLIENTPROJECTPATH=;
42         %LET _CLIENTPROJECTNAME=;
43         %LET _SASPROGRAMFILE=;
2                                                          The SAS System                                12:25 Monday, June 19, 2017

44         
45         ;*';*";*/;quit;run;
46         ODS _ALL_ CLOSE;
47         
48         
49         QUIT; RUN;
50         

 

Can you help me to manage it?

Thanks

Esteemed Advisor
Esteemed Advisor
Posts: 7,257

Re: Macro around several proc sql

Well, I don't use EG, so I don't know all the bits happening behind the scenes at these nodes.  What I can tell you is this note;

NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release.  Inserting white space 
             between a quoted string and the succeeding identifier is recommended.

Is when the code has some text directly following a quote.  In SAS there is named and date literals, for instance "01JAN2017"d has the d directly after the quote indicating that it is a date literal.  The note is telling you have something like this and in future SAS versions this may cause unexpected behaviour.

As a tip, you don't need the run; after SQL procs, just the quit;

Esteemed Advisor
Posts: 6,733

Re: Macro around several proc sql

You have unbalanced quotes somewhere in your code. This creates an unreasonably long string (see the WARNING) of text that should be "outside" of the string(s), and lets what you see as contents of a string be outside of the string, causing the NOTEs, as the assumed contents of a string follow immediately after the quotes.

 

This might even be caused by a macro variable containing quotes or similar.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 39

Re: Macro around several proc sql

[ Edited ]

Thanks for the reply.

 

But I don't think it is a string issue, because what I try to explain is, when I aggregate everything in only one "Program task" (I copy-paste everypart), it is working. I think it's more a guide issue, which doesn't want to split the macro in two parts, and the code inside macro in other parts. Am I wrong?

 

I tried a simple example:

I wrote this "program":

%macro C;
PROC SQL;
   CREATE TABLE WORK.B AS 
   SELECT a1
      FROM WORK.A;
QUIT;

%mend C;
%C;

It works.

 

When I wrote this in a first program task:

%macro C;

Then, this in a second program task:

PROC SQL;
   CREATE TABLE WORK.B AS 
   SELECT a1      
FROM WORK.A; QUIT; %mend C; %C;

And I link both tasks, It doesn't work...

Esteemed Advisor
Posts: 6,733

Re: Macro around several proc sql

[ Edited ]

Oh, you're splitting the macro across codes? BAD IDEA.

(Rereading your original post I saw it now. It's such a crazy idea that I didn't really see it at first).

 

EG always submits code automatically that is designed to clear up any kind of unbalanced things. Quotes, comments, unfinished steps (no run; ) or SQL (quit; )

Look at the log of any step you submitted in EG. You'll find

;*';*";*/;quit;run;

Now, asterisk-style comments don't work in macros, so the quotes are not masked while you're still "inside" a macro definition. This causes the unbalanced quotes.

 

Therefore:

Never split macros across code nodes in EG!

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 13

Re: Macro around several proc sql

Program1

 %macro c;
data x;
x=3;
run;
%mend;

 

Program2

%c;

 

First, you run the Program1,

Then you can run the Program2.

 

If you want to change script in Program1, you need to change it and rerun Program1. Otherwise,  will refer to the old version of the script

SAS Super FREQ
Posts: 676

Re: Macro around several proc sql

For every program node in the processflow. SAS Enterprise Guide does add some code at the beginning and at the end, see sample below:

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
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=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         
15         GOPTIONS ACCESSIBLE;
16         /* my code */
17         
18         GOPTIONS NOACCESSIBLE;
19         %LET _CLIENTTASKLABEL=;
20         %LET _CLIENTPROCESSFLOWNAME=;
21         %LET _CLIENTPROJECTPATH=;
22         %LET _CLIENTPROJECTPATHHOST=;
23         %LET _CLIENTPROJECTNAME=;
24         %LET _SASPROGRAMFILE=;
25         %LET _SASPROGRAMFILEHOST=;
26         
27         ;*';*";*/;quit;run;
28         ODS _ALL_ CLOSE;
29         
30         
31         QUIT; RUN;

So you can not have a program node with just the "%macro a;", does not make sense.

 

Why do you need to package all the SQL into a macro?

 

Within a processflow you can connect the various program (your individual Proc SQL) nodes in a specific sequence and run the complete branch.

Contributor
Posts: 39

Re: Macro around several proc sql

Thanks for the answer Bruno.

 

I need to join several huge volume table.

The biggest table can be split according to date range.

I want to split it on several period then join by looping on every period, then agregate the result in a final table...

That's why I need a macro.

And I don't want to do it in only one "Program" task, because it's not very readable...

SAS Super FREQ
Posts: 676

Re: Macro around several proc sql

So I suggest to do the following:

 

Create program nodes that contain your desired code and package it into a macro, somthing like

%macro sql01;
proc sql;
...
quit;
%mend;

Create a a control program that will look like this:

%sql01

%sql02

%sql03

In this way, you still have your smaller units, for editing etc. and you have your final control program that calls all the macros in the appropriate sequence, maybe even with parameters.

 

Bruno

Ask a Question
Discussion stats
  • 8 replies
  • 126 views
  • 0 likes
  • 5 in conversation