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

[ykk]

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.

[ChrisHemedinger]

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

[Quentin · Accepted solution]

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.

[ykk]

Thanks Quentin Call Execute Worked for me.

[ballardw]

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?

[ChrisHemedinger]

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