I am facing some problems in using Stored Process and will be very glad if someone can help me with the same.
Actually I have a program with both local and global macros but when I run the program through Stored Process the code errors.
Local Macros are not working in my Stored Process. What should I do to run the stored process with both local and global macros.
@DipeshGupta wrote:
Okay but then how to define them as output parameters in my stored process..??
OR how to make use of the URL created my the Stored Process..??
AND is there any reason behind stored process not working with the temporary library..??
I guess it really is time to follow Maxim1 and thoroughly study the Stored Process Developer's Guide: https://support.sas.com/documentation/cdl/en/stpug/68399/PDF/default/stpug.pdf
Page 23 and following deals with output parameters.
For your question 3: "clean environment"! This includes the WORK library, of course.
You will have to provide the code of your stored process so we can help you.
Okay.
None of my local Macros are running in Stored Process. Some of the examples are posted below.
data VARIABLE_LIST ;
set PROMPT ;
format category $100.;
LENGTH CATEGORY $7. ;
if %_eg_WhereParam( name , RF_SEL, IN , TYPE=S, IS_EXPLICIT=0 ) then category = "Factor";
ELSE IF %_eg_WhereParam( name , VT_SEL, IN , TYPE=S, IS_EXPLICIT=0 ) then category = "Variate";
ELSE IF %_eg_WhereParam( name , EXP_SEL, IN , TYPE=S, IS_EXPLICIT=0 ) then category = "Exposure";
ELSE IF %_eg_WhereParam( name , RES_SEL, EQ , TYPE=S, IS_EXPLICIT=0 ) then category = "Response";
else category = "Other";
RUN ;
This macro was created to build a dataset on the basis of values taken by prompts RF_SEL,RT_SEL,EXP_SEL,VT_SEL and is running perfectly in my program but when run on the stored process the code shows error.
%macro test(factorname , output , ID) ;
proc sort data=actshare.&DATA (keep= &factorname) out= &output nodupkey ;
by &factorname ;
run ;
data &output ;
set &output ;
format col1 $100. ;
format col2 $100. ;
col1 = "&factorname" ;
col2 = &factorname ;
ID = &ID ;
drop &factorname ;
rename col1 = RatingFactor ;
rename col2 = Levels ;
run ;
%mend test ;
%macro listsqlpub;
options symbolgen;
proc sql ;
select unique(NAME)
from SORTED
order by serialno ;
select unique(NAME)
into :pub1 - :pub&sqlobs
from SORTED
order by serialno;
%PUT &sqlobs;
select unique(factor)
from SORTED
order by serialno ;
select unique(factor)
into :fub1 - :fub&sqlobs
from SORTED
order by serialno;
quit ;
%PUT &sqlobs;
%do i=1 %to &sqlobs;
%test(&&pub&i , &&fub&i , &i) ;
%end;
proc sql ;
select unique(factor)
from sorted
order by serialno ;
%put &sqlobs ;
data FACTORS ;
MERGE factor1 - Factor&sqlobs ;
BY ID ;
IF first.ID THEN do ;
key = 0 ;
end ;
KEY + 1 ;
DROP ID ;
CATEGORY = "Factor" ;
RF1 = compress(cat(RatingFactor,Levels)) ;
run ;
%PUT &sqlobs;
%mend listsqlpub;
%listsqlpub
This macro was created to build various data sets factor1 factor2 etc and the names of the dataset and other values will taken from the data sorted and then all the data sets created will be merged into one dataset Factor and this also works perfectly in SAS program but not in Stored Process.
Similarly there are various macros in my project which are running perfectly in SAS program but not in Stored Process Program.
In the context of the SAS macro processor, "global" and "local" are definitions used for macro variables.
A local macro variable is in the local symbol table of the macro it is defined in, a global macro variable is in the global symbol table that is accessible throughout the program / SAS Process it was defined in.
Macros themselves are always global (accessible throughtout the SAS process).
So we need to clarify first what you mean by "global" and "local".
Second, it would be a great help to see the ERROR messages from the STP log that relate to the macros.
It could well be that the autocall settings for your workspace server and the STP server are not the same.
This is the first error when Stored Process is run.
And I am Defining local macros as those which are used as %macro and global as those which are used as %let=
@DipeshGupta wrote:
This is the first error when Stored Process is run.
And I am Defining local macros as those which are used as %macro and global as those which are used as %let=
WRONG.
%let sets macro variables, %macro defines macros. There is no scope here, with the execption of macro variables set within macros, which end up in the local symbol table unless they already existed globally or are named in a %global statement.
After some searching around, I found that %_eg_whereparam is a macro available only in Enterprise Guide. To use it in a stored process, you should extract the code from the server-init log in Enterprise Guide and save it to a .sas file that you %include in your stored process.
(EG sends the macro code to the workspace server when the session is initialized)
Agree with @Kurt_Bremser, the definition of _eg_whereparam is in the EG binary, rather than being a usual autocall macro. This means you can't use it outside of EG, unless you create your own macro definition. For more on locating the macro definition, see: https://communities.sas.com/t5/SAS-Enterprise-Guide/Where-to-find-Source-Code-for-all-EG-Macros/td-p...
I used the SAS Initialization log to get the %eg_whereparam code which looks something like this
%macro _eg_WhereParam( COLUMN, PARM, OPERATOR, TYPE=S, MATCHALL=_ALL_VALUES_, MATCHALL_CLAUSE=1, MAX= ,IS_EXPLICIT=0,MATCH_CASE=1);
%local q1 q2 sq1 sq2;
%local isEmpty;
%local isEqual isNotEqual;
%local isIn isNotIn;
%local isString;
%local isBetween;
%let isEqual = ("%QUPCASE(&OPERATOR)" = "EQ" OR "&OPERATOR" = "=");
%let isNotEqual = ("%QUPCASE(&OPERATOR)" = "NE" OR "&OPERATOR" = "<>");
%let isIn = ("%QUPCASE(&OPERATOR)" = "IN");
%let isNotIn = ("%QUPCASE(&OPERATOR)" = "NOT IN");
%let isString = (%QUPCASE(&TYPE) eq S or %QUPCASE(&TYPE) eq STRING );
%if &isString %then
%do;
%if "&MATCH_CASE" eq "0" %then %do;
%let COLUMN = %str(UPPER%(&COLUMN%));
%let q1=%str(UPPER%(%");
%let q2=%str(%"%));
%let sq1=%str(UPPER%(%');
%let sq2=%str(%'%));
%end;
%else %do;
%let q1=%str(%");
%let q2=%str(%");
%let sq1=%str(%');
%let sq2=%str(%');
%end;
%end;
%else %if %QUPCASE(&TYPE) eq D or %QUPCASE(&TYPE) eq DATE %then
%do;
%let q1=%str(%");
%let q2=%str(%"d);
%let sq1=%str(%');
%let sq2=%str(%');
%end;
%else %if %QUPCASE(&TYPE) eq T or %QUPCASE(&TYPE) eq TIME %then
%do;
%let q1=%str(%");
%let q2=%str(%"t);
%let sq1=%str(%');
%let sq2=%str(%');
%end;
%else %if %QUPCASE(&TYPE) eq DT or %QUPCASE(&TYPE) eq DATETIME %then
%do;
%let q1=%str(%");
%let q2=%str(%"dt);
%let sq1=%str(%');
%let sq2=%str(%');
%end;
%else
%do;
%let q1=;
%let q2=;
%let sq1=;
%let sq2=;
%end;
%if "&PARM" = "" %then %let PARM=&COLUMN;
%let isBetween = ("%QUPCASE(&OPERATOR)"="BETWEEN" or "%QUPCASE(&OPERATOR)"="NOT BETWEEN");
The SAS System 09:53 Saturday, June 17, 2017
%if "&MAX" = "" %then %do;
%let MAX = &parm._MAX;
%if &isBetween %then %let PARM = &parm._MIN;
%end;
%if not %symexist(&PARM) or (&isBetween and not %symexist(&MAX)) %then %do;
%if &IS_EXPLICIT=0 %then %do;
not &MATCHALL_CLAUSE
%end;
%else %do;
not 1=1
%end;
%end;
%else %if "%qupcase(&&&PARM)" = "%qupcase(&MATCHALL)" %then %do;
%if &IS_EXPLICIT=0 %then %do;
&MATCHALL_CLAUSE
%end;
%else %do;
1=1
%end;
%end;
%else %if (not %symexist(&PARM._count)) or &isBetween %then %do;
%let isEmpty = ("&&&PARM" = "");
%if (&isEqual AND &isEmpty AND &isString) %then
&COLUMN is null;
%else %if (&isNotEqual AND &isEmpty AND &isString) %then
&COLUMN is not null;
%else %do;
%if &IS_EXPLICIT=0 %then %do;
&COLUMN &OPERATOR %unquote(&q1)&&&PARM%unquote(&q2)
%end;
%else %do;
&COLUMN &OPERATOR %unquote(%nrstr(&sq1))&&&PARM%unquote(%nrstr(&sq2))
%end;
%if &isBetween %then
AND %unquote(&q1)&&&MAX%unquote(&q2);
%end;
%end;
%else
%do;
%local emptyList;
%let emptyList = %symexist(&PARM._count);
%if &emptyList %then %let emptyList = &&&PARM._count = 0;
%if (&emptyList) %then
%do;
%if (&isNotin) %then
1;
%else
0;
%end;
%else %if (&&&PARM._count = 1) %then
%do;
%let isEmpty = ("&&&PARM" = "");
%if (&isIn AND &isEmpty AND &isString) %then
&COLUMN is null;
%else %if (&isNotin AND &isEmpty AND &isString) %then
&COLUMN is not null;
%else %do;
%if &IS_EXPLICIT=0 %then %do;
&COLUMN &OPERATOR (%unquote(&q1)&&&PARM%unquote(&q2))
%end;
%else %do;
&COLUMN &OPERATOR (%unquote(%nrstr(&sq1))&&&PARM%unquote(%nrstr(&sq2)))
%end;
%end;
%end;
%else
%do;
%local addIsNull addIsNotNull addComma; %let addIsNull = %eval(0);
%let addIsNotNull = %eval(0);
%let addComma = %eval(0);
(&COLUMN &OPERATOR (
%do i=1 %to &&&PARM._count;
%let isEmpty = ("&&&PARM&i" = "");
%if (&isString AND &isEmpty AND (&isIn OR &isNotIn)) %then
%do;
%if (&isIn) %then %let addIsNull = 1;
%else %let addIsNotNull = 1;
%end;
%else
%do;
%if &addComma %then %do;,%end; %if &IS_EXPLICIT=0 %then %do;
%unquote(&q1)&&&PARM&i%unquote(&q2)
%end;
%else %do;
%unquote(%nrstr(&sq1))&&&PARM&i%unquote(%nrstr(&sq2))
%end;
%let addComma = %eval(1);
%end;
%end;)
%if &addIsNull %then OR &COLUMN is null;
%else %if &addIsNotNull %then AND &COLUMN is not null;
%do;)
%end;
%end;
%end;
%mend _eg_WhereParam;
Then I saved this program(MAC) at the location "Z:\GLM Model\Programs\MAC.sas"
Then I used the copy file task to transfer the file from the local PC server to sas workspace server
Uploading files from machine "MRK00012" to SAS session on "SASApp".
Using these settings:
Source file specification: Z:\GLM Model\Programs\MAC.sas
Destination path: E:\saswork\_TD26432_SGISASAPP1_\Prc2
Overwrite existing files: Yes
Resolve macro references in file paths: Yes
Convert line endings for text files: No
NOTE: Resolving macro expressions in file paths. SAS log:
1 The SAS System 11:02 Monday, June 19, 2017
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %let _egcopySource = Z:\GLM Model\Programs\MAC.sas;
4 %let _egcopyTarget = E:\saswork\_TD26432_SGISASAPP1_\Prc2;
5 %put Source files resolve to &_egcopySource;
Source files resolve to Z:\GLM Model\Programs\MAC.sas
6 %put Target folder resolves to &_egcopyTarget;
Target folder resolves to E:\saswork\_TD26432_SGISASAPP1_\Prc2
7
8 QUIT; RUN;
9
NOTE: Source file resolved to Z:\GLM Model\Programs\MAC.sas
NOTE: Target folder resolved to E:\saswork\_TD26432_SGISASAPP1_\Prc2
Uploading Z:\GLM Model\Programs\MAC.sas to E:\saswork\_TD26432_SGISASAPP1_\Prc2\MAC.sas ...
...Uploaded MAC.sas, 6,018 bytes
NOTE: Copied 6,018 bytes in 7.19 seconds.
NOTE: Total number of files processed: 1
After that I used the %include macro to call this whereparam macro in my soted process before my Whereparam code
%include "E:\saswork\_TD26432_SGISASAPP1_\Prc2\MAC.sas" ;
data VARIABLE_LIST ;
set Actshare.PROMPT ;
format category $100.;
LENGTH CATEGORY $7. ;
if %_eg_WhereParam( name , RF_SEL, IN , TYPE=S, IS_EXPLICIT=0 ) then category = "Factor";
ELSE IF %_eg_WhereParam( name , VT_SEL, IN , TYPE=S, IS_EXPLICIT=0 ) then category = "Variate";
ELSE IF %_eg_WhereParam( name , EXP_SEL, IN , TYPE=S, IS_EXPLICIT=0 ) then category = "Exposure";
ELSE IF %_eg_WhereParam( name , RES_SEL, EQ , TYPE=S, IS_EXPLICIT=0 ) then category = "Response";
else category = "Other";
RUN ;
But still it ends up in showing error.
34
35 GOPTIONS ACCESSIBLE;
36 %include "E:\saswork\_TD26432_SGISASAPP1_\Prc2\MAC.sas" ;
201
202 data VARIABLE_LIST ;
203 set Actshare.PROMPT ;
204 format category $100.;
205 LENGTH CATEGORY $7. ;
WARNING: Length of character variable category has already been set.
Use the LENGTH statement as the very first statement in the DATA STEP to declare the length of a character variable.
206
207 if %_eg_WhereParam( name , RF_SEL, IN , TYPE=S, IS_EXPLICIT=0 ) then category = "Factor";
NOTE: Line generated by the invoked macro "_EG_WHEREPARAM".
207 The SAS System 09:53 Saturday, June 17, 2017
___
388
76
ERROR 388-185: Expecting an arithmetic operator.
ERROR 76-322: Syntax error, statement will be ignored.
I cant understand where is the error in my code.
When you pull code from the log, you need to remove the page headers that are written into the log by SAS:
The SAS System 09:53 Saturday, June 17, 2017
Hey, Thanks, Now that code works perfectly.
I wonder how did I forget to remove that. 🙂
But I am facing one more problem with my Stored Process.
As I told you earlier I have several programs and each of my program has a prompt. All the programs work perfectly in my project but when I run them through the stored process it doesn't recognize the prompt values of previous programs.
for eg.
suppose My Program 1 has a prompt name DATA which takes the value "xyz"
and my program 2 has something like Work.&Data
But the Stored Process of Program 2 doesn't resolve DATA to xyz.
How to resolve that.
Any stored process run in the Stored Process Server gets its own clean environment at execution time, so macro variables set in one stored process do not "spill over" into the next one, unless you have defined them in your stored process definition as output parameters, or use other means (eg a STP creates a URL in a HTML FORM that includes parameters for the next STP)
Okay but then how to define them as output parameters in my stored process..??
OR how to make use of the URL created my the Stored Process..??
AND is there any reason behind stored process not working with the temporary library..??
@DipeshGupta wrote:
Okay but then how to define them as output parameters in my stored process..??
OR how to make use of the URL created my the Stored Process..??
AND is there any reason behind stored process not working with the temporary library..??
I guess it really is time to follow Maxim1 and thoroughly study the Stored Process Developer's Guide: https://support.sas.com/documentation/cdl/en/stpug/68399/PDF/default/stpug.pdf
Page 23 and following deals with output parameters.
For your question 3: "clean environment"! This includes the WORK library, of course.
Thankyou so much for all your help. My StoredProcess works perfectly well now although I was not able to crack Proc STP and still working on it but for the time being I have figured an alternative way to process my macro variable.
Thanks Again. 🙂
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.