BookmarkSubscribeRSS Feed
kykyn
Fluorite | Level 6
 

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

10 REPLIES 10
data_null__
Jade | Level 19

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;

Capture.PNG 

kykyn
Fluorite | Level 6

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

Kurt_Bremser
Super User

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.

kykyn
Fluorite | Level 6

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 😞

Kurt_Bremser
Super User

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.

data_null__
Jade | Level 19

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

kykyn
Fluorite | Level 6

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 :-)?

ballardw
Super User

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

Astounding
PROC Star

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.

 

 

kykyn
Fluorite | Level 6
To answer to balardw :
It s possible for state after but not for state before

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
  • 10 replies
  • 2336 views
  • 6 likes
  • 5 in conversation