BookmarkSubscribeRSS Feed
katrinaaa90
Calcite | Level 5

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

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

katrinaaa90
Calcite | Level 5

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

Astounding
PROC Star

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Steelers_In_DC
Barite | Level 11

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.

katrinaaa90
Calcite | Level 5

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

Steelers_In_DC
Barite | Level 11

So after three iterations you'll have 8 columns?

RichardDeVen
Barite | Level 11

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


Kurt_Bremser
Super User

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 4299 views
  • 1 like
  • 6 in conversation