SELECT INTO WITHIN DO LOOP AND MACRO VARIABLE

[tampham92]

Hi,

I am trying to execute the following code. I am try to get store different value of beta because it varies across A, B, and C. It does not give me updated value of beta.

%let prod = A B C;

%LET ns = %sysfunc(countw(&prod.));

%macro lookup_table;
%GLOBAL beta_up_&i. ;
%local i v1;
%do i=1 %to &ns.;
%let v1 = %scan(&prod, &i);

proc sql;

select beta_up into :beta_up_&v1.
from WORK.DEP_RATE_DATA_0002(where=( Account = "&v1"));

quit;

data test;

set master;

variable_want = &&beta_up_&v1.;

run;

%end;
%mend;
%lookup_table;

%put &&beta_up_&v1.;

And when I try to check the value of &&beta_up_&v1.; it gave me error.

WARNING: Apparent symbolic reference V1 not resolved.
ERROR: Invalid symbolic variable name &.
ERROR: Symbolic variable name V1. must contain only letters, digits, and underscores.
ERROR: Invalid symbolic variable name V1..
WARNING: Apparent symbolic reference V1 not resolved.
WARNING: Apparent symbolic reference V1 not resolved.

Can you please help me out? Thanks.

[Reeza]

Scope, your global declaration is before the i is declared so the macro variables are not created in the global table. Move it into your loop.

[tampham92]

I moved the Global statement into the loop and it ran. However, when I tried to test where the value is store using %put statement (%put &beta_up&v1) it just returned beta_up&v1. Do you know why?;

[Reeza]

You need more & in front of it. You had it correct in your initial post but in the code below you only have one. 

%let v1=A;
%let beta_upA = Testing;

*wrong;
%put &beta_up&v1;
*right;
%put &&beta_up&v1;

---

@tampham92 wrote:

I moved the Global statement into the loop and it ran. However, when I tried to test where the value is store using %put statement (%put &beta_up&v1) it just returned beta_up&v1. Do you know why?;

---

[Reeza]

This won't work though:

data test;

set master;

variable_want = &&beta_up_&v1.;

run;

You're always starting from the 'master' table and your test data will overwrite it each time. So you'll only be left with the results from the last loop, not all the results. 

---

@tampham92 wrote:

Hi,

I am trying to execute the following code. I am try to get store different value of beta because it varies across A, B, and C. It does not give me updated value of beta.

%let prod = A B C;

%LET ns = %sysfunc(countw(&prod.));

%macro lookup_table;
%GLOBAL beta_up_&i. ;
%local i v1;
%do i=1 %to &ns.;
%let v1 = %scan(&prod, &i);

proc sql;

select beta_up into :beta_up_&v1.
from WORK.DEP_RATE_DATA_0002(where=( Account = "&v1"));

quit;

data test;

set master;

variable_want = &&beta_up_&v1.;

run;

%end;
%mend;
%lookup_table;

%put &&beta_up_&v1.;

And when I try to check the value of &&beta_up_&v1.; it gave me error.

WARNING: Apparent symbolic reference V1 not resolved.
ERROR: Invalid symbolic variable name &.
ERROR: Symbolic variable name V1. must contain only letters, digits, and underscores.
ERROR: Invalid symbolic variable name V1..
WARNING: Apparent symbolic reference V1 not resolved.
WARNING: Apparent symbolic reference V1 not resolved.

Can you please help me out? Thanks.

---

[RW9 · Accepted solution]

Its not entirely clear to me what you are trying to achieve.  From that snippet of code, it looks like you are taking one item from a dataset, and then putting that into a macro variable, which you are then inserting into a dataset?  This really can't be the best way to achieve your end result.  SAS provides much functionality for merging, setting, updating data, so why try to code it by hand?  Just from what I can see there (and you have not provided any test data/required output so its just guess work);

data test;
  merge  master dep_rate_data_0002 (where=(account="&v1.") keep=beta_up);
run;

Should work, or if the variable exists, use update or sql insert.  Plenty of options.  Avoid using global macro variables, its bad practice and can lead to very hard to debug issues (as global affects the whole session).