topic Re: proc sql in do loop in macro with %eval in SAS Programming

proc sql in do loop in macro with %eval

katrinaaa90 — Thu, 28 May 2015 11:40:45 GMT

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

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

RW9 — Thu, 28 May 2015 12:27:50 GMT

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;

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

Steelers_In_DC — Thu, 28 May 2015 12:36:13 GMT

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.

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

Kurt_Bremser — Thu, 28 May 2015 12:36:19 GMT

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.

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

katrinaaa90 — Thu, 28 May 2015 13:18:32 GMT

this is what I have:

my_text_column	my_numeric_column	active
period_201401	4	1
period_201401	4	1
period_201401	4	0
period_201402	5	1
period_201402	5	0
period_201402	5	0

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

my_text_column	my_numeric_column	sum_of_period_5	sum_of_period_6
period_201401	4	2	.
period_201402	5	.	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

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

katrinaaa90 — Thu, 28 May 2015 13:27:42 GMT

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...

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

Steelers_In_DC — Thu, 28 May 2015 13:33:00 GMT

So after three iterations you'll have 8 columns?

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

Astounding — Thu, 28 May 2015 13:55:42 GMT

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.

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

RW9 — Thu, 28 May 2015 13:56:44 GMT

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 0

period_201402 5 1

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;

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

RichardDeVen — Thu, 28 May 2015 14:09:40 GMT

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 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

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 . . . . . . . ...

[this seems weird]

month day sum_dayforward2 ... sum_dayforward32

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

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

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