topic Re: Problem with incrementation of a macro variable in SAS Programming

Problem with incrementation of a macro variable

kykyn — Thu, 10 Mar 2016 13:33:54 GMT

Hello everyone,

I come to you for a problem that I am sure is stupid to solve, but I blocked it for a while so I want good external advice .

1) I created 10 macro variables (&ma_mv_i) , with i going from 1 to 10 .

2) I want to create a column in my SAS table with the idea that the line “n” will be given the value of the macro variable “&ma_mv_n”

i tried this :

%let i=0;
data matable;
set masource;
format new_var 11.;
%let i = %EVAL(&i+1);
new_var = &&ma_mv_&i;
run;

==> and i don't understand why, but "i" does not increment. Indeed, at the end of the program its value is 2, while i thought it should be 10..

if you have an idea to resolve it should be great to me 🙂

Good day and thank you in advance

Re: Problem with incrementation of a macro variable

data_null__ — Thu, 10 Mar 2016 13:51:28 GMT

This is a job for SYMGET.

%let mv1=One;
%let mv2=Two;

data macs;
   set sashelp.class(obs=2);
   length mv $8;
   mv = symget(cats('MV',_n_));
   run;
proc print;
   run;

Re: Problem with incrementation of a macro variable

Kurt_Bremser — Thu, 10 Mar 2016 14:05:41 GMT

You fell into the common trap of assuming that the macro language in SAS is intended to do something.

Instead it is intended to generate program text that will then do something.

Your next misconception is about the time at which macro statements are resolved vs the time when data is handled in a data step.

Let's go through your code stepwise:

%let i=0;

Now the macro variable i is set to zero, no program text was generated.

The SAS interpreter then loads the following into its input buffer:

data  matable;
set  masource;
format new_var 11.;

for later compilation

now it encounters a macro character and invokes the macro engine:

%let i = %EVAL(&i+1);

Now, the macro variable i has been set to 1, no program text was generated. The macro engine hands back to the main SAS interpreter.

The main interpreter encounters

new_var = &&ma_mv_&i;

and once again invokes the macro engine to resolve

&&ma_mv_&i

After macro resolving, &&ma_mv_&i is replaced first with &ma_mv_1 and then with the contents of macro variable ma_mv_1; to make it easier, assume that &ma_mv_1 contained the string 1000.

so, after macro processing, this

new_var = 1000;

is buffered for compilation.

Now,

run;

is read and the data step enters compilation phase in this form:

data  matable;
set  masource;
format new_var 11.;
  new_var = 1000;
run;

If this is syntactically correct, the data step will start execution.

As you see, nothing that has to do with macros is present when the data step executes.

Re: Problem with incrementation of a macro variable

kykyn — Thu, 10 Mar 2016 14:08:21 GMT

oh yes of course SYMGET!!!

Thanks a lot for your answer, i really didn't to try that!

and a big thanks also to Kurt for his explication

Re: Problem with incrementation of a macro variable

kykyn — Thu, 10 Mar 2016 14:15:06 GMT

to your opinion, is it possible to do the SYMGET function with 35 000 mv?

i ask that because the soluce on my program and it's very very long to execute 😞

Re: Problem with incrementation of a macro variable

Kurt_Bremser — Thu, 10 Mar 2016 14:20:24 GMT

Why don't you put your 35000 values into a dataset and merge that into your existing dataset?

Or read the 35000 values from a dataset into a hash object if you need some fancier kind of assignment mechanism?

What you try to do sounds much more like a data operation, which should be done in a data step or proc sql; macro is for dynamically creating program code where such is needed.

Re: Problem with incrementation of a macro variable

data_null__ — Thu, 10 Mar 2016 14:20:49 GMT

You have 35,000 macro variables? You don't want to do it that way.

Re: Problem with incrementation of a macro variable

Astounding — Thu, 10 Mar 2016 14:26:42 GMT

Could you possibly store your 35,000 macro variables in a different format? To illustrate, suppose you had a SAS data set called MA_MV that contained 35,000 observations and just a single variable: MA_MV_value. You could then code:

data want;

set MA_MV;

set ma_source;

run;

You wouldn't need to create NEW_VAR ... it is already in the data using the variable name MA_MV_value. Now this DATA step would halt as soon as one of the two data sets ran out of observations so you would have to be sure you have the right number of observations in both data sets. But it's a simple solution (if it is, in fact, a solution for what you need).

Good luck.

Re: Problem with incrementation of a macro variable

kykyn — Thu, 10 Mar 2016 14:42:51 GMT

So, i m french so it's not very easy to be clear but i m going to try to show you my reasoning

I have a SAS table with 3 variables : STATE_BEFORE / STATE_AFTER / DATE

this data is a liste of customers number who can change at some dates

For exemple :

STATE_BEFORE STATE_AFTER DATE

100 200 01/01

300 400 01/01

200 600 01/02

600 800 01/03

As you can see, at the end, the customer 300 is now known as customer 400, and the 100 is known as 800

My goal is to create a table like that:

STATE_BEFORE STATE_AFTER

100 800

300 400

So, how i thought i could do that :

1) i keep the numbers who are and colum "STATE_AFTER" and "STATE_BEFORE" ==> in my example, we have the 200 and the 600

for information, at the beginning i have a table with 360 000 lines, and when i keep only the ones of step 1), i have a new table with 35 000 lines.

this table has the colums ""STATE_AFTER"" and "ROWCOL"

the ROWCOL is the line number in my first table with the 360 000 lines

2) i create a mv for each of this 35 000 numbers, so in my example we have :

mv1=200

mv2=600

3) for each mv, i read the first table, the one with 360 000 lines, and when i found the value of my MV in the column ""STATE_BEFORE" , i replace the value of my MV by the value of the column "STATE_AFTER"

Exemple :

mv1=200

i see it on line 3 in the column ""STATE_BEFORE" , so its value become 600.

i continue to read the table, and on the line 4, become will become 800

so at the end, mv1=800

and of course we also have mv2=800.

4)now, and its here that i have a pb with my program, i work with my table who has 35 000 lignes

its the same table than at the step2, so i know that mv1 is value for the line

with the code i showed you, i create a new variable that contains the new value of the MV

5) last step, i join the 2 tables withe the colum "ROWNO" and at the end i have what i wanted

is it clear :-)?

Re: Problem with incrementation of a macro variable

ballardw — Thu, 10 Mar 2016 17:36:58 GMT

Are the values in State_before or State_after ever duplicated within that variable?

For example is this possible?

STATE_BEFORE STATE_AFTER DATE

100 200 01/01

300 400 01/01

200 600 01/02

600 800 01/03

300 500 01/04

900 400 01/05

Re: Problem with incrementation of a macro variable

kykyn — Thu, 10 Mar 2016 21:51:45 GMT

To answer to balardw :
It s possible for state after but not for state before