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
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;
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...
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.
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;
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.
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
So after three iterations you'll have 8 columns?
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 . . . . ...
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.