topic Re: assign macro variable value to a new variable, proc sql in SAS Programming

assign macro variable value to a new variable, proc sql

fierceanalytics — Wed, 01 Jun 2022 22:57:49 GMT

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

Re: assign macro variable value to a new variable, proc sql

ballardw — Wed, 01 Jun 2022 23:11:58 GMT

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.

Re: assign macro variable value to a new variable, proc sql

WarrenKuhfeld — Wed, 01 Jun 2022 23:18:35 GMT

Look for any discrepancies in these two lines:

%do i = 1 %to 12;

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

Re: assign macro variable value to a new variable, proc sql

fierceanalytics — Thu, 02 Jun 2022 00:18:58 GMT

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

Re: assign macro variable value to a new variable, proc sql

SASKiwi — Thu, 02 Jun 2022 01:12:33 GMT

%let name=boston;

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

Re: assign macro variable value to a new variable, proc sql

fierceanalytics — Thu, 02 Jun 2022 20:42:11 GMT

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

Re: assign macro variable value to a new variable, proc sql

SASKiwi — Thu, 02 Jun 2022 21:25:09 GMT

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;

Re: assign macro variable value to a new variable, proc sql

fierceanalytics — Fri, 03 Jun 2022 19:35:24 GMT

Good. Thanks