Desktop productivity for business analysts and programmers

Problems STored Proces macro parameters

Accepted Solution Solved
Reply
Contributor
Posts: 63
Accepted Solution

Problems STored Proces macro parameters

 

I got a stored process with a lot of macro parameters. But I can not get it to work properly. The parameters does not work in the selection.

 

Can you see what is wrong and why the paramters will not work???? it is especially the two date macro parameters: "&fradato."d and

"&tildato."d

 

Some code from the stored process:


%macro udtrak;

data idataperiode fordataperiode;

set test;

 

%if %nrquote(&banavn) eq ' '  or %nrquote(&banavn) eq _ALL_VALUES_ %then %do;
 if transaction_date_d >="&fradato."d and  transaction_date_d <="&tildato."d and regime_number in("&ordning") then output idataperiode;
 if transaction_date_d < "&fradato."d and regime_number in("&ordning") then output fordataperiode;
%end;

%if %nrquote(&fsnavn) eq ' ' or %nrquote(&fsnavn)eq _ALL_VALUES_%then %do;

if transaction_date_d >="&fradato."d and  transaction_date_d <="&tildato."d and regime_number in("&ordning") and ba_id in("&banavn")
 then output idataperiode;
if transaction_date_d <"&fradato."d and regime_number in("&ordning") and ba_id in("&banavn") then output fordataperiode;
%end;


%if %nrquote(&fsnavn) ne ' ' and %nrquote(&banavn) ne ' ' %then %do;
if transaction_date_d >="&fradato."d and  transaction_date_d <="&tildato."d and regime_number in("&ordning")
and ba_id in("&banavn") and focusarea_id in("&fsnavn") then output idataperiode;

if transaction_date_d <"&fradato."d  and regime_number in("&ordning") and
ba_id in("&banavn") and focusarea_id in("&fsnavn")   then output fordataperiode;
%end;
%mend udtrak;
run;
%udtrak;

 

 


Accepted Solutions
Solution
‎06-26-2016 03:21 AM
Super User
Posts: 5,352

Re: Problems STored Proces macro parameters

[ Edited ]

There are at least two issues in what you posted.

 

It looks like  you are trying to see if a macro variable has a null value by using:

 

%if %nrquote(&banavn) eq ' '

 

That's the wrong way to do it.  While there are many working ways, this is my preference:

 

%if %length(&banavn) eq 0

 

The code that you have now is actually checking whether the macro variable is three characters long, with the first and third characters equal to a single quote.

 

Second, the RUN statement is in the wrong place.  Right now, it follows the definition of the macro.  It has to be moved later, following the invocation of the macro:

 

%udtrak

run;

 

The RUN statement actually could become part of the macro definition instead.  (There are cases where the separate RUN statement gives the macro more flexibility, providing the ability to add more statements between the macro invocation and the RUN statement.  This particular DATA step has OUTPUT statements within the macro definition, so that added flexibility would not be useful.)

 

There may be more that I haven't seen, but cleaning up these two issues should be a good start.

View solution in original post


All Replies
Solution
‎06-26-2016 03:21 AM
Super User
Posts: 5,352

Re: Problems STored Proces macro parameters

[ Edited ]

There are at least two issues in what you posted.

 

It looks like  you are trying to see if a macro variable has a null value by using:

 

%if %nrquote(&banavn) eq ' '

 

That's the wrong way to do it.  While there are many working ways, this is my preference:

 

%if %length(&banavn) eq 0

 

The code that you have now is actually checking whether the macro variable is three characters long, with the first and third characters equal to a single quote.

 

Second, the RUN statement is in the wrong place.  Right now, it follows the definition of the macro.  It has to be moved later, following the invocation of the macro:

 

%udtrak

run;

 

The RUN statement actually could become part of the macro definition instead.  (There are cases where the separate RUN statement gives the macro more flexibility, providing the ability to add more statements between the macro invocation and the RUN statement.  This particular DATA step has OUTPUT statements within the macro definition, so that added flexibility would not be useful.)

 

There may be more that I haven't seen, but cleaning up these two issues should be a good start.

Contributor
Posts: 63

Re: Problems STored Proces macro parameters

Thank you very much. It did help and it looks nice. I am very happy thanks.

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 241 views
  • 0 likes
  • 2 in conversation