topic Re: proc sql into variable in SAS Programming

proc sql into variable

Jane7476 — Wed, 26 Nov 2014 14:00:12 GMT

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

Re: proc sql into variable

RW9 — Wed, 26 Nov 2014 14:12:02 GMT

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.

Re: proc sql into variable

Jane7476 — Wed, 26 Nov 2014 14:14:42 GMT

Hi,

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

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

Thanks

Re: proc sql into variable

RW9 — Wed, 26 Nov 2014 14:31:44 GMT

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.

Re: proc sql into variable

Jane7476 — Thu, 27 Nov 2014 09:24:25 GMT

Thanks a ton.

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