Desktop productivity for business analysts and programmers

Stored Process Macro

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

Stored Process Macro

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.


Accepted Solutions
Solution
‎06-21-2017 01:51 AM
Esteemed Advisor
Posts: 6,646

Re: Stored Process Macro


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Esteemed Advisor
Posts: 6,646

Re: Stored Process Macro

You will have to provide the code of your stored process so we can help you.

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

Re: Stored Process Macro

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 Smiley Tongueub1 - Smiley Tongueub&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.

Esteemed Advisor
Posts: 6,646

Re: Stored Process Macro

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.

 

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

Re: Stored Process Macro

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=

Attachment
Esteemed Advisor
Posts: 6,646

Re: Stored Process Macro


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)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,213

Re: Stored Process Macro

Agree with @KurtBremser, 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...

 

 

Contributor
Posts: 40

Re: Stored Process Macro

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.

Esteemed Advisor
Posts: 6,646

Re: Stored Process Macro

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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 40

Re: Stored Process Macro

Hey, Thanks, Now that code works perfectly.

I wonder how did I forget to remove that. Smiley Happy

 

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.

 

Esteemed Advisor
Posts: 6,646

Re: Stored Process Macro

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)

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

Re: Stored Process Macro

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

Solution
‎06-21-2017 01:51 AM
Esteemed Advisor
Posts: 6,646

Re: Stored Process Macro


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.

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

Re: Stored Process Macro

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. Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 238 views
  • 1 like
  • 3 in conversation