## Stuck with coding a simple (?) macro.

Moderators: statman, Analyst Techy, andris, Fierce, GerineL, Smash

shijin83
Posts: 7
Joined: Wed Jul 18, 2012 3:30 pm

### Stuck with coding a simple (?) macro.

Dear fellow SPSS Users.

I spent hours searching the net and reading tutorials, but I simply didn't find a solution for my problem. Maybe somebody here is able to help me out or at least point me in the right direction. I hope I will be able to explain my problem in an easy to understand way (I am not native English, so my apologies for any mistakes).

The Situation:
I faced myself with a task where I needed to manually calculate 350 different variables in SPSS so I decided to put in the effort and try and learn coding Macros and save a lot of time in the future. I am a newbie to programming, though, and ultimately I wasn't able to elaborate a working macro to solve my problem.

I have three variables in a dataset that I need to do some calculations with:
Agglo00_No stores numeric values for categories ranging from 1 to 50.
st_typ stores numeric values for categories ranging from 1 to 3.
METERS stores travel distances as numeric values.

Now, I would like to create a new variable for each combination of the categories (stored in the first two variables) which leads to having to calculate 350 new variables.

The Problem:
With my knowledge at the moment, I would have to calculate the variables manually. This is not a very dynamic and a very tedious task to do, so I decided to (try and) use a macro. The way I would code the syntax manually is as follows:

Code: Select all

``````DATASET ACTIVATE segmente_agglo.
IF (Agglo00_No = 1 & st_typ = 1) a01_1=METERS.
IF (Agglo00_No = 2 & st_typ = 1) a02_1=METERS.
.
.
IF (Agglo00_No = 1 & st_typ = 2) a01_2=METERS.
IF (Agglo00_No = 2 & st_typ = 2) a02_2=METERS.
.
.
.
and so on...``````
Looking at this, the macro I want would create the variables for each AggloNr and each St_Typ and name them accordingly (a0X_Y | X being the value of AggloNr and Y being the value of St_Typ).
After hours of testing different options, reading reference manuals and tutorials and browsing the web I coded the following macro to do the above procedure 350 times:

Macro code:

Code: Select all

``````DEFINE PW_Agglo_ST_Typ (AggloNo = !TOKENS(1) /  St_Typ = !TOKENS(1) / meters = !TOKENS(1))

!DO !I = 1 !TO 50
!IF (!St_Typ = 1) !THEN !LET !varname = !CONCAT ('a','0',!I,'_',!St_Typ).
!LET !t = !meters.
RENAME VARIABLES (t = !varname).
!IFEND
!IF (!St_Typ = 2) !THEN !LET !varname = !CONCAT ('a','0',!I,'_',!St_Typ).
!LET !t = !meters.
RENAME VARIABLES (t = !varname).
!IFEND
!IF (!St_Typ = 3) !THEN !LET !varname = !CONCAT ('a','0',!I,'_',!St_Typ).
!LET !t = !meters.
RENAME VARIABLES (t = !varname).
!IFEND
!DOEND

!ENDDEFINE.``````
Macro call:

Code: Select all

``````SET MPRINT ON.
PW_Agglo_ST_Typ AggloNo = Aglo00_No St_Typ = st_typ meters = METERS.
SET MPRINT OFF.``````
The underlying assumption is, that the loop would run 50 times, once for each value stored in the variable AggloNr (since this variable is a list ranging from 1 to 50). Each time a loop would run, three variables will be created, one for each value of St_Typ (which only ranges from 1 to 3).

But when I run the script, nothing happens. Although there are no errors displayed, there is also no result to be seen. The output looks like this:

Code: Select all

``````DEFINE PW_Agglo_ST_Typ (AggloNo = !TOKENS(1) /  St_Typ = !TOKENS(1) / meters = !TOKENS(1))

!DO !I = 1 !TO 50
!IF (!St_Typ = 1) !THEN !LET !varname = !CONCAT ('a','0',!I,'_',!St_Typ).
!LET !t = !meters.
RENAME VARIABLES (t = !varname).
!IFEND
!IF (!St_Typ = 2) !THEN !LET !varname = !CONCAT ('a','0',!I,'_',!St_Typ).
!LET !t = !meters.
RENAME VARIABLES (t = !varname).
!IFEND
!IF (!St_Typ = 3) !THEN !LET !varname = !CONCAT ('a','0',!I,'_',!St_Typ).
!LET !t = !meters.
RENAME VARIABLES (t = !varname).
!IFEND
!DOEND

!ENDDEFINE.
SET MPRINT ON.
PW_Agglo_ST_Typ AggloNo = Agglo00_No St_Typ = st_typ meters = METERS.
708  0 M>
709  0 M>  .
710  0 M>
711  0 M>  .
SET MPRINT OFF.
712  0 M>  SET MPRINT OFF.``````
I know it is a lot to ask and many of you get questions like this all over and are busy themselves. But at the moment I really have no clue as to where I am wrong. Clearly I am overlooking something, but I don't see it. A nudge in the right direction would already be appreciated or - of course - a code example that might work.

Thank you very much in advance!

Sincerely,

Jonas Bley
Penguin_Knight
Posts: 473
Joined: Thu Apr 05, 2012 5:58 pm

### Re: Stuck with coding a simple (?) macro.

Code: Select all

``````DEFINE HeyHello ().
!DO !J = 1 !TO 3
!DO !I = 1 !TO 9
IF Agglo00_No = !I & st_typ = !J !CONCAT("a0", !I, "_", !J) = METERS .
!DOEND
!DO !I = 10 !TO 50
IF Agglo00_No = !I & st_typ = !J !CONCAT("a", !I, "_", !J) = METERS .
!DOEND
!DOEND
!ENDDEFINE .
EXECUTE .

HeyHello
EXECUTE .``````
shijin83
Posts: 7
Joined: Wed Jul 18, 2012 3:30 pm

### Re: Stuck with coding a simple (?) macro.

Dear PenguinKnight

Thank you so much! You really made my day
I also really like your proposition to split the loop into two parts two guarantee an orderly nomenclature of the variables.

Once again: Thank you!

Best regards

Jonas
Penguin_Knight
Posts: 473
Joined: Thu Apr 05, 2012 5:58 pm

### Re: Stuck with coding a simple (?) macro.

You're welcome. Keep practicing, macro writing is a very useful skill.
shijin83
Posts: 7
Joined: Wed Jul 18, 2012 3:30 pm

### Re: Stuck with coding a simple (?) macro.

It is incredible how useful these macros are, I really hope to get a hang on it soon!

I have a follow-up question to this problem. Now my variables will be named using a combination of the variables Agglo00_No and st_typ very nicely. However, I would like to create variable labels that conatain elements stored in yet another variable: Name. I tried out several possibilities, but I didn't manage.

As far as I know, we can use Macros to create SPSS Syntax that will the be treated the same way regular syntax is. However, it is a bit confusing to distinct the way variables are handled in macro script language and in SPSS Syntax.

I tried to do the following:

Code: Select all

``````DEFINE PW_AGGLO_ST_TYP (!POSITIONAL, !TOKENS(1)).
!DO !J = 1 !TO 3
!DO !I = 1 !TO 9
IF (Row_ID = !I & st_typ = !J) !CONCAT("a0", !I, "_", !J) = METERS.
!LET !VarLabel = !1
VARIABLE LEVELS  !CONCAT("a0", !I, "_", !J) (SCALE).
VARIABLE LABELS  !CONCAT("a0", !1, "_", !J) !VarLabel.
!DOEND
!DO !I = 10 !TO 50
IF (Row_ID = !I & st_typ = !J) !CONCAT("a", !I, "_", !J) = METERS.
!LET !VarLabel = !1
VARIABLE LEVELS  !CONCAT("a", !I, "_", !J) (SCALE).
VARIABLE LABELS  !CONCAT("a0", !1, "_", !J) !VarLabel.
!DOEND
!DOEND
!ENDDEFINE .
EXECUTE.

PW_AGGLO_ST_TYP Name
EXECUTE.
``````
I assumed that the script would then read for each iteration of the loop the corresponding value of the variable Name and I could store it in the Variable !VarLabel. Later I could then use it with the VARIABLE LABELS command to assign the corresponding label to each variable during the loop processing.
But it appears that in !1 is not the respective value of the variable Name stored, but simply the name of the Variable (i.e. a string reading 'Name') resulting in the Variable Labels becoming a0Name_X for all the variables. Is there a way to read the value of the variable Name that corresponds to the entry with the value of Row_ID and use it for the creation of the Variable Labels?

I tried out several constructs, but I am just not getting there and would therefore, again, appreciate any help or hints I can get.

Thank you very much in advance!

Sincerely,

Joans
Penguin_Knight
Posts: 473
Joined: Thu Apr 05, 2012 5:58 pm

### Re: Stuck with coding a simple (?) macro.

shijin83 wrote:I have a follow-up question to this problem. Now my variables will be named using a combination of the variables Agglo00_No and st_typ very nicely. However, I would like to create variable labels that conatain elements stored in yet another variable: Name. I tried out several possibilities, but I didn't manage.
^ Not sure what this means. Can you just simply describe the steps you'd do with perhaps an example? One thing that confuses me is that why would you want to create a variable with the variable names?
shijin83
Posts: 7
Joined: Wed Jul 18, 2012 3:30 pm

### Re: Stuck with coding a simple (?) macro.

Hi PenguinKnight

Thank you again for your fast help! Of course I am glad to try to explain my thought process a bit better: I am still working with the same data set where I use the macro as you have written it for me. This generates 350 variables but none of them has a label. So I would like to create a variable Label that is different for each variable.

These are the variables I already introduced in the first post:
Agglo00_No stores numeric values for categories ranging from 1 to 50.
st_typ stores numeric values for categories ranging from 1 to 3.
METERS stores travel distances as numeric values.

Now there is also a variable called (simply) Name.
Name is a string variable containing 50 names, each one of them corresponds to a number in the variable Agglo00_No.

So some example cases would be:

Code: Select all

``````Agglo00_No | st_typ | METERS | Name
1           3       23    Oxford
1           1       67    Oxford
2           2       43    Stanford
``````
So Agglo00_No and Name always match each other. Now the macro creates a name for each variable that contains the values of Agglo00_No and the st_typ. In addition I would like to have the Macro also create Variable Labels, but they should contain the value from the variable Name rather than the value from Agglo00_No (i.e. I'd like to have the name of the Agglomeration in the label, rather than just the number). In addition, I would like to also include the value from st_type into the label. So for the two lines from the example tables, the Variable labels should look like this:

VARIABLE LABELS a01_3 'Oxford_3'
VARIABLE LABELS a01_1 'Oxford_1'
VARIABLE LABELS a02_2 'Stanford_2'

I tried several different solutions, but I simply did not manage to properly get the right value from the Variable 'Name' for each Iteration of the loop.
Penguin_Knight
Posts: 473
Joined: Thu Apr 05, 2012 5:58 pm

### Re: Stuck with coding a simple (?) macro.

Variable label command will be quite difficult to build into the if loop because while there are k cases fulfill the if condition, there are (n-k) cases in the column that don't and variable label applies to the whole column. I am not sure how to do that.

A work-around is the make a aggregate dataset by Agglo00_No, st_type, and Name. Then you'll have a compact list of unique combinations. Use concat command to "write" your variable label syntax as a string variable, like:

Code: Select all

``````STRING MySyntax (A100) .
COMPUTE MySyntax = CONCAT("VARIABLE LABELS a", Agglo00_No, "'", Name, "_", st_typ, "' .") .
EXECUTE .``````
Then cut and paste the whole column of MySyntax as your working syntax.
shijin83
Posts: 7
Joined: Wed Jul 18, 2012 3:30 pm

### Re: Stuck with coding a simple (?) macro.

Dear PenguinKnight

I was almost afraid that there was no easy solution as I tried out several options. But your idea for a workaround is very helpful, I am sure I will find something out!
Thank you very much for your constant efforts. I really hope I get better at this soon

Best regards

Jonas

### Who is online

Users browsing this forum: No registered users and 1 guest