Proc sql as a macro

[ren2010]

Hi
I have situation where i have to update a table with in a data step,can I do this by calling the proc sql as a macro from the data step.
eg:
options symbolgen mprint;
%macro excl(id=,sal=);
proc sql;
Update tt
Set &id=.,
&sal=.
Where &id In (Select id From ttt)
and &sal In (Select sal From ttt)
;
QUIT;
%mend;
data tt;
set rr;
if id ne . then %excl(id=,sal=);
run;

[SASKiwi]

The simple answer is no! You are trying to run SQL inside a DATA step. SAS steps, either DATA steps or procedures (including the SQL procedure) run sequentially, one after the other, never one inside the other.

You can do your updates all within a DATA step but you need to explain in more detail what you are trying to do.

[ren2010]

Thanks.
I came to know that if I use call execute and call macro which has PROC SQL from a data step it will work.

[DataShare]

calling a macro is supported from a data step ,
but it should not contain the proc sql execution as the next step while the data step is executing,
so for your case you can write the macro calls to the file in the data step and then after data step execution you can include that file so that the calls get executed.

[Cynthia_sas]

Hi:
Just a comment to clarify. Technicallly, you could not code a PROC SQL step -inside- a DATA step program. So, that can make the CALL EXECUTE solution seem confusing, because the type of the CALL (EXECUTE) implies that the called code is being executed from within the DATA step. It really should be named the
"PUT-IN-THE-STACK-TO" EXECUTE call routine....but CALL EXECUTE is so much more brief.

But, all that's really happening is that any macro call generated inside a DATA step with CALL EXECUTE is placed into a holding area or stack. None of the code, let me repeat, NONE of the code, is executed while the DATA step is executing. The code (macro call with dataset variable values) is placed into the stack. THEN, as soon as the DATA step program is over and the step boundary has been reached, all the code that's been placed in the stack starts to execute. This means you can build your macro call using dataset variable values, but that the generated macro call will not actually run until the DATA step program ends.

cynthia

[Flip]

"if I use call execute and call macro which has PROC SQL from a data step it will work."

It will work, but the generated code is executed after the data step, not during it. The code is stored in a buffer and executed as if it was an included file.