DATA Step, Macro, Functions and more

proc sql in do loop in macro with %eval

Reply
New Contributor
Posts: 3

proc sql in do loop in macro with %eval

Hi,

I need some help with the case.

I have something like this:

%macro my_macro;

     %do i = 1 %to 3;

          proc sql;

               create table my_table as

               select

                    my_text_column,

                    my_numeric_column,

                    my_numeric_column + 100 as new_name_%eval(my_numeric_column + &i.)

               from other_table

     %end;

%mend;

%my_macro;

And here's the error I get:

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:

       my_numeric_column + 1


How can make a column with a name that contains equation with sql column value?

For example when my_numeric_column equals 15 and i=1, the result I expect is new column name new_name_16

Thanks for the reply

katrinaaa90

Super User
Super User
Posts: 7,955

Re: proc sql in do loop in macro with %eval

Posted in reply to katrinaaa90

You cannot do that.  my_numeric_column is part of the Base SAS language, i.e. its in a dataset.  &i and the loop are part of the macro language.  At the time the macro pre-processor is working, i.e. when it is resolving the macro code, there is no dataset available - so the variable does not exist, nor does the proc sql.  That is all later on at compile time.  The macro pre-processor is there to expand on the text given using its rules.  Give example test data in a datastep of what you want have and example of what you want to achieve.  For example, you could use arrays:

data want;

     set have;

     array new_name_{3} 8.;

     do i=1 to 3;

          new_name_{i}=my_numeric_column+100;

     end;

run;

New Contributor
Posts: 3

Re: proc sql in do loop in macro with %eval

That explains why it won't work. I wasn't aware of the sequence of this steps.

But... the name of the new column still depends on the value from the dataset, so it cannot be done with simple array.

As far as I understand it is not possible inside the macro. Will it work if I do "do loop" through i = 1 to 3 outside the macro?

Like that:

do i = 1 to 3;

          proc sql;

               create table my_table as

               select

                    my_text_column,

                    my_numeric_column,

                    sum(active) as new_name_eval(my_numeric_column + &i.)

               from other_table

          quit;

end;

edit: right... do loop cannot be done outside data step...

Super User
Posts: 5,509

Re: proc sql in do loop in macro with %eval

Posted in reply to katrinaaa90

You will need to separate out the creation of variable names from the creation of your data set.  For example, this might be a start to create a string that holds the proper variable names:

proc sql;

   select distinct 'sum_of_period' || put(my_numeric_column, 1.) into : variable_names separated by ' ' from have;

   %put &variable_names;

Then add another SELECT statement to actually extract data and populate the proper names.  That might be a difficult task, but you need to begin by separating this into two steps.

Super User
Super User
Posts: 7,955

Re: proc sql in do loop in macro with %eval

Posted in reply to katrinaaa90

Pretty straightforward then.  Do your summing on the normlised data, then transpose it up:

data have;

  infile datalines dlm=" ";

  length my_text_column $20;

  input my_text_column $ my_numeric_column active;

datalines;

period_201401 4 1

period_201401 4 1

period_201401 4 0

period_201402 5 1

period_201402 5 0

period_201402 5 0

;

run;

proc sql;

  create table INTER as

  select  MY_TEXT_COLUMN,

          MY_NUMERIC_COLUMN,

          SUM(ACTIVE) as RES

  from    HAVE

  group by MY_TEXT_COLUMN,

           MY_NUMERIC_COLUMN;

quit;

proc transpose data=inter out=want prefix=sum_of_period_;

  by my_text_column;

  var res;

  id my_numeric_column;

  idlabel my_numeric_column;

run;

Valued Guide
Posts: 860

Re: proc sql in do loop in macro with %eval

Posted in reply to katrinaaa90

Can you give an example of what you have and what you want?  I think this can be solved with a sum statement and an equation but I'm having trouble picturing what you are trying to do.

New Contributor
Posts: 3

Re: proc sql in do loop in macro with %eval

Posted in reply to Steelers_In_DC

this is what I have:

my_text_columnmy_numeric_columnactive
period_20140141
period_20140141
period_20140140
period_20140251
period_20140250
period_20140250



and this is what I want for the first iteration of the loop (i=1):

my_text_columnmy_numeric_columnsum_of_period_5sum_of_period_6
period_20140142.
period_2014025.1

so the SQL could be just sum(active) ... group by my_text_column or sum(case when active=1 then 1 end)

the sum is not the problem - the name is

Valued Guide
Posts: 860

Re: proc sql in do loop in macro with %eval

Posted in reply to katrinaaa90

So after three iterations you'll have 8 columns?

Occasional Contributor
Posts: 9

Re: proc sql in do loop in macro with %eval

Posted in reply to katrinaaa90

This is a common issue when someone want to reshape data from a useful data model to a reporting or summarization data model.

Many times it is far better to just use the reporting tool and place data in your meta-data; such as when you move the period value into part of an array of column names.

You can use Proc REPORT or TABULATE for reporting.

Proc TRANSPOSE and MEANS can be used to reshape the data.  Likewise a DATA STEP with an array and DOW loop processing can reshape and summarize in one pass.

With regards to the sample, what do you want when more than one 'my_numeric' value occurs within a 'my_text_column'' ?

Can we call column1 as month and column2  as day ?

data have;

length month $15 day value 8;

input month day value;

datalines;

X 4 1

X 4 1

X 4 0

X 5 1

X 5 2

X 5 3

X 5 4

X 5 5

X 5 6

Y 5 1

Y 5 0

Y 5 0

run;

Do you want

[this seems somewhat reasonable if you drop the day column]

month day sum_dayforward2 ... sum_dayforward32

X ? . . . 2 15 . . . . . . . ...

Y ? . . . 2 1 . . . . . . . ...


or


[this seems weird]

month day sum_dayforward2 ... sum_dayforward32

X 4 . . . 2 . . . . . ...

X 5 . . . . 15 . . . . ...

Y 5 . . . . 1 . . . . ...


Super User
Posts: 7,782

Re: proc sql in do loop in macro with %eval

Posted in reply to katrinaaa90

You cannot create variables dynamically on the contents of other variables while a proc sql or a data step is running. The structure of the datasets is set while the step is compiled, and cannot change thereafter.

This would also be physically impossible, as the structure of a SAS dataset is written into its header page and determines the layout of all following records, and therefore can't change while the dataset is being written.

You need to completely revise your logic.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 9 replies
  • 1506 views
  • 1 like
  • 6 in conversation