proc sql into variable

[Jane7476]

Hi All,

I have written the below to put all my rmse output from an earlier regression macro into macro variables.

If I run the below sql statement without inserting into macro (but using macro variables) it runs fine. If I run it with macro variable then i do get a log which says

proc sql; select _rmse_ into: rmse_5 from regout5; quit;

However when I do %put &rmse_5 it says macro variable not found.

Code I am running is:

options mprint;

%macro merge3;

%do i=1 %to 5;

proc sql; select _RMSE_ into :rmse_&i from regout&i;

%end;

quit;

%mend;

%merge3;

Question is: What is wrong with the above macro which is leading to %put &rmse_5 saying macro variable not found.

Thanks

[RW9]

Well, two things.  First, would need the full log, i.e. does regout1-regout5 actually exist, and is there a variable _rmse_ in each one?

Personally I finds there's rarely a need to create lots of macro variables.  Provide a some sample data, required output or process.  By group processing, arrays etc. are far quicker and more effective than creating lists of arrays to loop over.

[Jane7476]

Hi,

There is a data set regout1-5 and _rmse_ also exists.

Unfortuantely don't have access to upload data from this machine.

Thanks

[RW9 · Accepted solution]

For test data I meant just make something up and post a dataset statement here, like:

data regout1;
  _rmse_=1; output;
run;
data regout2;
  _rmse_=4; output;
run;
%global rmse_1;
%global rmse_2
options mprint symbolgen mlogic;
%macro merge3;
  %do i=1 %to 2;
    proc sql;
      select  _RMSE_
      into    :rmse_&i   
      from    regout&i;
    quit;
  %end;
%mend;
%merge3;

%put &rmse_1.;
%put &rmse_2.;

Anyway, the reason is that the macro variables in the macro are considered to be Local to the macro.  If you put:

%global rmse_1;

%global rmse_2;... etc. before your code as I have done above it should work.

However, why do it like that?  What are you using these macro variables for, better ways exist for most scenarios.  You will run in to further issues later, i.e. how many are there, how to loop over them etc.

[Jane7476]

Thanks a ton.

I should have realised earlier that wasn't declaring it as a global variable.