topic Re: Can I call a macro process which builds a dataset inside RESOLVE? in SAS Programming

Can I call a macro process which builds a dataset inside RESOLVE?

mrzd — Tue, 10 Apr 2012 07:56:29 GMT

Hi Experts,

Hope you could enlighten me with this. I have a quite challenging problem using macros. Is it possible to call a procedure inside the sql? I think it will be better to show it in an example.

PROGRAM A:

%macro a;

%let test_value = test_val;

&test_value

%mend a;

data sample;

msg = "this is a test";

run;

proc sql;

create table a as

select msg, RESOLVE('%a') as val

from sample

;

quit;

proc print data = a;

run;

The output of these codes is the ff:

MSG VAL

this is a test test_val

So as I understand, RESOLVE uses the value in %a. But what if aside from value resolution, an exception handling procedure is also embedded? Is this possible? I tried doing this code:

PROGRAM B:

%macro a;proc_,

%let test_value = test_val;

&test_value

%if &test_value = test_val %then %do;

data excep_dat;

val= “There is data exception.”;

run;

%end;

%mend a;

Supposed to be, the expected output is that it will also produce a dataset named excep_dat.

But what the output is shows is:

Obs msg val

1 this is a test test_val data excep_dat; val= “There is data exception.”; run;

Is there a way to allow the production of the dataset? Or if this procedure is not valid, hope you could confirm so that we can think of another step to handle it. Thanks!

Re: Can I call a macro process which builds a dataset inside RESOLVE?

manojinpec — Tue, 10 Apr 2012 08:08:29 GMT

Hi,

macro just substitute the resolved program as text in your macro call.in the first instance when you invoke the macro in proc sql it substitute the macro program text which is text_val in the place where you invoke the macro.

However in second the macro program will resolve to may statement including data step which is not valid in proc sql.

you can write proc sql to achieve your result conditinally.

Re: Can I call a macro process which builds a dataset inside RESOLVE?

Ksharp — Tue, 10 Apr 2012 08:16:57 GMT

No. Resolve() can't do that.If you want to make different datasets during reading a dataset ,use Call execute(),

which can generate dynamic code ,and these code is executed after the data step.

Ksharp

Re: Can I call a macro process which builds a dataset inside RESOLVE?

shivas — Tue, 10 Apr 2012 08:38:35 GMT

Hi,

Try this...hope this helps..

%macro a;

%let test_value = test_val;

This is a test;

%mend a;

data sample;

msg = "this is a test";

run;

data two;

set sample;

x=resolve('&test_value');

y=resolve('%a');

run;

proc sql;

create table a as

select msg, RESOLVE('&test_value') as val,Resolve('%a') as val2

from sample

;

quit;

Thanks,

Shiva