BookmarkSubscribeRSS Feed
ren2010
Obsidian | Level 7
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;
5 REPLIES 5
SASKiwi
PROC Star
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
Obsidian | Level 7
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
SAS Employee
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
SAS Super FREQ
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
Fluorite | Level 6
"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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 1030 views
  • 0 likes
  • 5 in conversation