CODEWITHSUNDEEP
macrossasproc-sql
Wayne
Wayne

Reputation: 33

use sas select into: statement to create global macro variables

I wish to use select into: to create a global variable in a macro module.

%macro example();
   proc sql noprint;
      select x into:DDD from aaa;
   quit;
%mend;

The problem is I wish to create a global variable DDD, in this example we can only get the local variable DDD. Thanks. Wayne.

Upvotes: 1

Views: 4837

Answers (3)

Surya
Surya

Reputation: 1

I know its quite an old post. But if someone is still trying to figure this out. Specifically in the case we don't know the number of macro variables a sql query would produce, we can use %do iterative loop for i=1 to &sqlobs. Then use %global statement inside it to produce global macro variable and then assign values of the local sql macros to it. The method is shown below. I also assigned the sqlobs local macro to a global macro so that, it is also available globally just incase.

%macro DistinctVal(tab,col);
    proc sql;
     select distinct &col 
     into :DVal1 -
     from &tab;
    quit;
    %global distinctobs;
    %let distinctobs=&sqlobs; 
    /*We do this step to convert the local sqlobs value to Global distinctobs values*/

    %do i=1 %to &distinctobs %by 1;
     %global DistinctVal&i;
     %let DistinctVal&i=&&Dval&i;
    %end;
    /*We do this step to convert the local Dval1-.... values to Global 
      DistinctVal1-... values*/

%mend DistinctVal;

Upvotes: 0

DaBigNikoladze
DaBigNikoladze

Reputation: 661

As @Reeza pointed out you can simply specify that you want the macro-variable global at the beginning of your macro with the %global DDD; statement.

Just one focus point:

sometimes you can recall a macro from inside another macro. In this case you could have the same macro-variable pre-existing as local in the outer-macro. Recalling the inner-macro with inside a %global statement you could encounter an error due to the conflict between the global and local definition of the same macro-variable (ERROR: Attempt to %GLOBAL a name (XXX) which exists in a local environment.).

In this case you can:

  • pre-define the macro-variable as global also in the outer-macro
  • pre-define the macro-variable as local (or simply give it a (non-)value with a %let, sql into: or call symput) in the outer-macro avoiding the %global statement in the inner-macro

depending on the macro-variable visibility requirement you have.

Upvotes: 0

Reeza
Reeza

Reputation: 21264

I don't think you can control the scope within Proc SQL. You an use call symputx in a data step to specify the scope.

However, macro variables created in open code, via Proc SQL, are global by default. Within a macro it will be local.

As noted in the comments you can also explicitly declare a macro variable as global using 

 %global macro_variable_name;

Upvotes: 1

Related Questions