Monday, December 6, 2010

Resolve vs Symget

Symget function enjoys lot more amount of pulicity and usage as compared to the resolve function, though the later one actually is (at least according to me..) more efficient, powerful and more flexible! (Guess am becoming more sentimental these days..)

Having this in mind, i tried searching about some articles on resovle function in google but in vain. There are lot more number of articles and examples of symget usage and call execute functions as compared to the resolve functions which accomplishes both these functionality.

Resolve function resolves the value of the text expression during the data step execution. It can reslove the value of a macro variable (like symget) and also expand the macro invokation (somewhat similar to call execute, just that it expands the macro and doesn't execute it...)

I've illustrated below, a few simple examples of various uses of Resolve funtion.

Illustration 1: Macro variable resolution in a datastep (similar to symget)

data t;
dt = symget(sysdate9.);
dt1 = resolve('&sysdate9.');
run;

Both the variables in the above datastep returns a character variale of length 200 each having the value of the current date in date9. format.

Illustration 2: Mutiple macro variable resolution in a datastep (extention from symget)

data t;
dt = symget('sysdate9')||' '||symget('systime');
dt1 = resolve('&sysdate9. &systime.');
run;

We can also use resolve function to resolve multiple macro variables (which is unavailable in symget).

Illustration 3: Expansion of a Macro using resolve

%macro min;
select min(age)
from sashelp.class
%mend min;


%macro m(i);
proc sql;
create table tab12 as
select *
from sashelp.class
where age=&i;
quit;
%mend m;

data _null_;
call execute(resolve('%m(%min)'));
run;

In the above example, I use resolve function along with the call execute function which would expand the macro invokation twice (though a single resolve function is being used). Thus, the above data step does a call execute once and resolves the %m which inturn takes the arguement as %min which in turns expands. So the result code would be like this, which is shown in the log:

NOTE: CALL EXECUTE generated line.

1 + proc sql;
1 + create table tab12 as select * from sashelp.class where age=select min(age) from sashelp.class;
1 + quit;

NOTE: Table WORK.TAB12 created, with 2 rows and 5 columns.

Illustration 4: Conditional execution and execution in a data step loop

%macro sql(i,minage,maxage);

%if &i=&minage %then %do;
   Proc Sql Noprint;
   Create table tab as
   select *
   from sashelp.class
   where age = &i
%end;

%else %if &i=&maxage %then %do;
   UNION ALL
   select *
   from sashelp.class
   where age = &i;
   Quit;
%end;


%else %do;
   UNION ALL
   select *
   from sashelp.class
   where age = &i
%end;
%mend sql;

proc sql noprint;
select min(age), max(age) into : minage, : maxage
from sashelp.class;
quit;

data _null_;
do i=11 to 16;
  call execute(resolve('%sql('||i||',&minage,&maxage)'));
end;
run;

In the above example, I'm trying to append multiple datasets which are created out of sashelp.class (for each age values). Here, I'm trying to execute the %sql macro in a datastep loop, and passing both the macro variable and the data step variable as the macro parameters.

This example also shows how to conditionally execute the macro variable based on the parameters passed.

I've pasted below the log message for the above code. Also note that the iteration number is printed at the begining of the every iteration's resolution.

NOTE: CALL EXECUTE generated line.



1 + Proc Sql Noprint;
1 + Create table tab as select * from sashelp.class where age = 11
2 + UNION ALL select * from sashelp.class where age = 12
3 + UNION ALL select * from sashelp.class where age = 13
4 + UNION ALL select * from sashelp.class where age = 14
5 + UNION ALL select * from sashelp.class where age = 15
6 + UNION ALL select * from sashelp.class where age = 16;
6 + Quit;

NOTE: Table WORK.TAB created, with 19 rows and 5 columns.

2 comments:

  1. excellent and very very helpful

    ReplyDelete
  2. This website can live streaming , you can join at my site :
    agen judi online terpercaya
    Prediksi Bola

    Thank you
    agenpialaeropa.club
    gamesonline.ga
    beritasemasaterikini.net

    ReplyDelete