DATA Step, Macro, Functions and more

proc sql into variable

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

proc sql into variable

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


Accepted Solutions
Solution
‎11-26-2014 09:31 AM
Super User
Super User
Posts: 7,401

Re: proc sql into variable

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.

View solution in original post


All Replies
Super User
Super User
Posts: 7,401

Re: proc sql into variable

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.

Contributor
Posts: 22

Re: proc sql into variable

Hi,

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

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

Thanks

Solution
‎11-26-2014 09:31 AM
Super User
Super User
Posts: 7,401

Re: proc sql into variable

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.

Contributor
Posts: 22

Re: proc sql into variable

Thanks a ton.

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 304 views
  • 0 likes
  • 2 in conversation