assign macro variable value to a new variable, proc sql

[fierceanalytics]

There should be easy ways. 

"%do i = 1 %to 12;

proc sql; create temp from select 

mean(p_&&s&j)  as price, ....

run;

%end;

.....

"

when the loop ends, &&s&i should have gone through 12 values, say, 12 months. How to insert a new variable, called MONTH, in the same proc sql operation, so the variable price is broken down by MONTH (which contains values of &&s&i)? Thank you

[ballardw]

Your code base will replace the same data set, Temp, eleven times and only have the result of the last i value.

You are leaving a LOT out of your problem description, such as where does the macro variable J get assigned, and the &s part of the &&s&j.

If your i is supposed to loop through different variables that contain a months values then the data is structured incorrectly for best use in SAS. Generally a separate variable holding the value of month and a single variable with the value works best. That would avoid having to "loop" through anything as then you could process the data "by month" directly..

Suggest providing an example of the data in the form of a data step and the desired result.

[WarrenKuhfeld]

Look for any discrepancies in these two lines:

%do i = 1 %to 12;

mean(p_&&s&j)  as price, .

[fierceanalytics]

Thank. Let us forget the do loop. I vs. j was just typo.

%let name=boston;
How to load &name to a new variable called LOC, inside proc sql; alongside getting max, mean… regular sql stuff? Thanks

[SASKiwi · Accepted solution]

%let name=boston;

proc sql;
  create table want as
  select "&name" as LOC
         ,Column1
         ,Column2
  from Have
  where ........;
quit;

[fierceanalytics]

Thank you. 

The next question is : how to regulate length of the new variable LOC? if first pass-through is Boston, LOC gets 6? That works for Huston or Dallas fully, but NY or SF may get truncated? I rephrased my question the second time. You probably sensed this is a do-loop context from my first time of asking with that i and J typo. Thanks. Jia

[SASKiwi]

Add a LENGTH option, for example setting it to 20 characters:

%let name=boston;

proc sql;
  create table want as
  select "&name" length = 20 as LOC
         ,Column1
         ,Column2
  from Have
  where ........;
quit;

[fierceanalytics]

Good. Thanks