topic Turning local macro variable into global using proc sql into: in SAS Programming

Turning local macro variable into global using proc sql into:

mhodge20 — Fri, 30 Jun 2017 17:54:07 GMT

I am creating macro variables inside of a do loop using the proc sql's into:, I understand that because it is inside a macro function that they are local. How do I transform to global? %global statement doesnt seem to be working.

This is the error

ERROR: Attempt to %GLOBAL a name (IDS2) which exists in a local environment. SAS 9.3

%macro num;
	proc sql;
		create table list1 as
		select distinct customerid from payments;
	quit;

   	%do i = 1 %to 6;
	
   		proc sql noprint;
   			select customerid into: ids&i separated by ' '
   			from list1
				where monotonic() between ((6553*&i)-6552) and (6553*&i); 
	
   		quit;
   %global ids&i;
                %end;
   %mend num;
   %num;

Re: Turning local macro variable into global using proc sql into:

ballardw — Fri, 30 Jun 2017 18:03:43 GMT

Did you try:

%macro num;
	proc sql;
		create table list1 as
		select distinct customerid from payments;
	quit;

   	%do i = 1 %to 6;
   %global ids&i;
	
   		proc sql noprint;
   			select customerid into: ids&i separated by ' '
   			from list1
				where monotonic() between ((6553*&i)-6552) and (6553*&i); 
	
   		quit;
                %end;
   %mend num;
   %num;

Re: Turning local macro variable into global using proc sql into:

Reeza — Fri, 30 Jun 2017 19:29:46 GMT

Looks like you're creating a series of macro variables based on the number of observations?

It may be worthwhile to consider a data step solution instead, where you don't really need a macro at all and can use CALL SYMPUTX to create the macro variable.

data want;
set have;
length value_list $32562.;

if _n_ =1 then i=1;

value_list = catx(" ", trim(value_list), id);

if _N_ >= i*6534 then do;
call symputx ('myvar'||i, value_list, 'g');
i+1;
call missing(value_list);
end;

run;

Here's an untested sketch of how that may look.