BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DipeshGupta
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

View solution in original post

13 REPLIES 13
DipeshGupta
Calcite | Level 5

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.

Kurt_Bremser
Super User

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.

 

DipeshGupta
Calcite | Level 5

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=


Capture.PNG
Kurt_Bremser
Super User

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

Quentin
Super User

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

 

 

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
DipeshGupta
Calcite | Level 5

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.

Kurt_Bremser
Super User

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
DipeshGupta
Calcite | Level 5

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.

 

Kurt_Bremser
Super User

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)

DipeshGupta
Calcite | Level 5

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

Kurt_Bremser
Super User

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

DipeshGupta
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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