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
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;
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.
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...
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!
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
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.
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...
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
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.