Desktop productivity for business analysts and programmers

Statement is not valid or it is used out of proper order with Proc SQL inside Macro

Accepted Solution Solved
Reply
Contributor ykk
Contributor
Posts: 24
Accepted Solution

Statement is not valid or it is used out of proper order with Proc SQL inside Macro

Below is the code I was using

%macro ins(ruleset_id);

proc sql noprint;

insert into RAS.RATE_ASSIGNMENT_BIZ

(BRM_RULESET_ID,

BRM_RULESET_NAME,

RATE_ASSIGNMENT_NAME,

XREF_ANALYSIS_ID,

ANALYST_ID,

EXISTING_GLOBAL_MIN ,

EXISTING_GLOBAL_MEDIAN,

EXISTING_GLOBAL_MAX,

EXISTING_LOCAL_MIN,

EXISTING_LOCAL_MEDIAN,

EXISTING_LOCAL_MAX,

SALES_LEVEL_1_DESCRIPTION,

ELEMENT,

PRICE_LIST_NAME,

ACTION_STATUS,

ACTION_REASON,

ANALYST_ID,

ANALYST_NAME)

SELECT BRM_RULESET_ID,BRM_RULESET_NAME,RATE_ASSIGNMENT_NAME,XREF_ANALYSIS_ID,ANALYST_ID,

EXISTING_GLOBAL_MIN ,

EXISTING_GLOBAL_MEDIAN,

EXISTING_GLOBAL_MAX,

EXISTING_LOCAL_MIN,

EXISTING_LOCAL_MEDIAN,

EXISTING_LOCAL_MAX,

SALES_LEVEL_1_DESCRIPTION,

ELEMENT,

PRICE_LIST_NAME,

ACTION_STATUS,

ACTION_REASON,

ANALYST_ID,

ANALYST_NAME

FROM progress.RAS_COMPLETED_RATES

WHERE BRM_RULESET_ID = &ruleset_id;

run;

%mend ins;

I am getting the erorr "ERROR 180-322: Statement is not valid or it is used out of proper order." When I call this macro inside a datasetp.

Can you please help me with the issue here.


Accepted Solutions
Solution
‎12-12-2014 03:57 PM
Trusted Advisor
Posts: 1,213

Re: Statement is not valid or it is used out of proper order with Proc SQL inside Macro

Hi,

Can you please show the code where you call the macro?

If by "call this macro inside a datastep" you mean something like:

data want;

  set have;

  %ins(5)

 

  *more data step code here;

run;

You will have problems, because you would be trying to run a PROC SQL step inside a data step, which doesn't work.   The proc SQL step will end the data step, and any data step code after the macro invocation will cause an error like you're getting.

If you are trying to generate a bunch of macro calls from data, look into call execute.  It allows you to generate the PROC SQL code, but the code is not actually executed until after the data step is completed.

HTH,

--Q.

View solution in original post


All Replies
Community Manager
Posts: 2,692

Re: Statement is not valid or it is used out of proper order with Proc SQL inside Macro

You cannot call a PROC SQL step within a DATA step, and since this macro expands to a full PROC SQL step, the resulting syntax is invalid.

If you really need this step to execute for every iteration (observation) in a DATA step, you can use DOSUBL to invoke it.

Here's a paper on the topic:

http://support.sas.com/resources/papers/proceedings12/227-2012.pdf

And some examples from my blog:

Tracking progress in your program with SAS Enterprise Guide: another trick

Chris

Solution
‎12-12-2014 03:57 PM
Trusted Advisor
Posts: 1,213

Re: Statement is not valid or it is used out of proper order with Proc SQL inside Macro

Hi,

Can you please show the code where you call the macro?

If by "call this macro inside a datastep" you mean something like:

data want;

  set have;

  %ins(5)

 

  *more data step code here;

run;

You will have problems, because you would be trying to run a PROC SQL step inside a data step, which doesn't work.   The proc SQL step will end the data step, and any data step code after the macro invocation will cause an error like you're getting.

If you are trying to generate a bunch of macro calls from data, look into call execute.  It allows you to generate the PROC SQL code, but the code is not actually executed until after the data step is completed.

HTH,

--Q.

Contributor ykk
Contributor
Posts: 24

Re: Statement is not valid or it is used out of proper order with Proc SQL inside Macro

Thanks Quentin Call Execute Worked for me.

Grand Advisor
Posts: 10,210

Re: Statement is not valid or it is used out of proper order with Proc SQL inside Macro

How are you call this in a data set?

If you paste the above code with a value for &ruleset_id into the data step currently calling this macro will it run?

Community Manager
Posts: 2,692

Re: Statement is not valid or it is used out of proper order with Proc SQL inside Macro

And one more idea, as I examine the SQL code a bit more.  It looks like you're trying to "enrich" another data set with some SAS Business Rules Manager output.  Do you think that you might instead be able to refactor your INSERT/SELECT code into a JOIN where you combine the two data sources based on matching criteria?  If you're using SAS Enterprise Guide, you might be able to play with the Query Builder to join the two data sets into your desired output -- or at least an output that you could then append back on to the RAS.RATE_ASSIGNMENT_BIZ target table.

Chris

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 1029 views
  • 3 likes
  • 4 in conversation