BookmarkSubscribeRSS Feed
mrzd
Calcite | Level 5

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!


3 REPLIES 3
manojinpec
Obsidian | Level 7

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.

Ksharp
Super User

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

shivas
Pyrite | Level 9

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;

or

proc sql;

create table a as

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

from sample

;

quit;

Thanks,

Shiva

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 755 views
  • 0 likes
  • 4 in conversation