Macro to relabel variables - help needed

[ksven]

Hi there -
I am probably missing something very easy here. I am trying to cut down on the syntax to relabel variables.
Let's assume I have two variables I1 and I2 and the labels are "James" and "Paul". I have been trying to set up the syntax using !do ...! commands but it does not work with an outer and inner loop. Basically I am looking for a "do repeat" that works with the relabel command.
Thanks for any help!
Best,
Sven

set mprint on.
define !relabel (vnames=!charend ("/") /vnames1=!cmdend)
!do !cnt !in (!vnames)
!do !cnt1 !in (!vnames!)
variable labels !concat("I",!cnt) !cnt1.
!doend.
!doend.
!enddefine.

!relabel vnames=1 2 /vnames1="James" "Paul".

[Bruce Weaver]

ksven wrote

Hi there -
I am probably missing something very easy here. I am trying to cut down on the syntax to relabel variables.
Let's assume I have two variables I1 and I2 and the labels are "James" and "Paul". I have been trying to set up the syntax using !do ...! commands but it does not work with an outer and inner loop. Basically I am looking for a "do repeat" that works with the relabel command.
Thanks for any help!
Best,
Sven

set mprint on.
define !relabel (vnames=!charend ("/") /vnames1=!cmdend)
!do !cnt !in (!vnames)
!do !cnt1 !in (!vnames!)
variable labels !concat("I",!cnt) !cnt1.
!doend.
!doend.
!enddefine.

!relabel vnames=1 2 /vnames1="James" "Paul".

If I follow, you don't want a nested loop (which will give all combinations of values), rather you want to loop through once, taking the variables in matched pairs.  David Marso did that in the "Post Number 2" example here:

   http://www.angelfire.com/wv/bwhomedir/spss/SLR_macro_v2.txt

For your macro, I think it would be something like this:


define !relabel (vnames=!charend ("/") /vnames1=!cmdend)

!LET !vnamescopy=!vnames

!do !cnt !in (!vnames)

**PARALLEL PROCESS VARIABLE LISTS **
!DO !cnt !in (!vnames)  
!LET !cnt1 = !HEAD(!vnamescopy)
!LET !vnamescopy=!TAIL(!vnamescopy)

variable labels !concat("I",!cnt) !cnt1.

!doend.

!enddefine.

!relabel vnames=1 2 /vnames1="James" "Paul".

HTH.

[ksven]

Hi Bruce - that was helpful.
I did manage to get this to work:

define !relabel (vnames=!charend ("/") /vnames1=!cmdend)
!LET !vnamescopy=!vnames1
!DO !cnt !in (!vnames)  
!LET !cnt1 = !HEAD(!vnamescopy)
!LET !vnamescopy=!TAIL(!vnamescopy)
variable labels !concat("I",!cnt) !cnt1.
!doend
!enddefine.
!relabel vnames=1 2 3/vnames1="James" "Paul" "Mary".

The one thing that is not working though is that I want to use a macro that contains all of my variable labels and insert it in this macro:

DEFINE !list1()
"James"
"Paul"
"Mary"
!ENDDEFINE.

so that I would invoke the macro as follows: !relabel vnames=1 2 3/vnames1=!list1.

That does not work though ...?

Any ideas?
Thanks!
Sven

Bruce Weaver wrote

ksven wrote

Hi there -
I am probably missing something very easy here. I am trying to cut down on the syntax to relabel variables.
Let's assume I have two variables I1 and I2 and the labels are "James" and "Paul". I have been trying to set up the syntax using !do ...! commands but it does not work with an outer and inner loop. Basically I am looking for a "do repeat" that works with the relabel command.
Thanks for any help!
Best,
Sven

set mprint on.
define !relabel (vnames=!charend ("/") /vnames1=!cmdend)
!do !cnt !in (!vnames)
!do !cnt1 !in (!vnames!)
variable labels !concat("I",!cnt) !cnt1.
!doend.
!doend.
!enddefine.

!relabel vnames=1 2 /vnames1="James" "Paul".

If I follow, you don't want a nested loop (which will give all combinations of values), rather you want to loop through once, taking the variables in matched pairs.  David Marso did that in the "Post Number 2" example here:

   http://www.angelfire.com/wv/bwhomedir/spss/SLR_macro_v2.txt

For your macro, I think it would be something like this:


define !relabel (vnames=!charend ("/") /vnames1=!cmdend)

!LET !vnamescopy=!vnames

!do !cnt !in (!vnames)

**PARALLEL PROCESS VARIABLE LISTS **
!DO !cnt !in (!vnames)  
!LET !cnt1 = !HEAD(!vnamescopy)
!LET !vnamescopy=!TAIL(!vnamescopy)

variable labels !concat("I",!cnt) !cnt1.

!doend.

!enddefine.

!relabel vnames=1 2 /vnames1="James" "Paul".

HTH.

[Bruce Weaver]

ksven wrote

Hi Bruce - that was helpful.
I did manage to get this to work:

define !relabel (vnames=!charend ("/") /vnames1=!cmdend)
!LET !vnamescopy=!vnames1
!DO !cnt !in (!vnames)  
!LET !cnt1 = !HEAD(!vnamescopy)
!LET !vnamescopy=!TAIL(!vnamescopy)
variable labels !concat("I",!cnt) !cnt1.
!doend
!enddefine.
!relabel vnames=1 2 3/vnames1="James" "Paul" "Mary".

The one thing that is not working though is that I want to use a macro that contains all of my variable labels and insert it in this macro:

DEFINE !list1()
"James"
"Paul"
"Mary"
!ENDDEFINE.

so that I would invoke the macro as follows: !relabel vnames=1 2 3/vnames1=!list1.

That does not work though ...?

Any ideas?
Thanks!
Sven

I know that using macros for variable lists can be a bit tricky.  One of my colleagues wrote a little document a few years ago on some of the problems he encountered.  I don't think it addresses your problem specifically, but here it is anyway:

   http://www.angelfire.com/wv/bwhomedir/spss/macros_for_var_lists.txt


I've found that quotes can be a bit finicky in macros too.  One thing you could try is changing this line:

  variable labels !concat("I",!cnt) !cnt1.

to this:

  variable labels !concat("I",!cnt) !QUOTE(!cnt1).

To call it, then:

!relabel vnames=1 2 3/vnames1=James Paul Mary.

Or with the !list macro:

DEFINE !list1() James Paul Mary
!ENDDEFINE.

!relabel vnames=1 2 3/vnames1=!list1.

It probably won't make any difference, but that's what I'd try for starters.

David Marso...if you're reading this, please step in and straighten us out!  ;-)

[ksven]

Hi Bruce -
thanks for the suggestion. The !quote command works.

Substituting the var list with the macro still does not work though ... this is the error message I get:

(....

!doend
3339 M>  !doend
!enddefine.
3340 M>   !enddefine.
!relabel vnames=1 2 3/vnames1=!list1.
3341 M>  
3342 M>  .
3343 M>  variable labels I1 ''.
3344 M>  variable labels I2 ''.
3345 M>  variable labels I3 ''.
3346 M>  .
3347 M>  .
3348 M>  .
3349 M>  .
3350 M>  .
3351 M>  .
3352 M>  .
3353 M>  James Mary John

>Error # 1.  Command name: James
>The first word in the line is not recognized as an SPSS command.
>This command not executed.

3354 M>   .

(...)


I read through the link you gave me and changed the syntax of my macro generating the variable list, but got the same error message independent of the changes. Not sure what the problem is (?)

Best,
Sven

Bruce Weaver wrote

ksven wrote

Hi Bruce - that was helpful.
I did manage to get this to work:

define !relabel (vnames=!charend ("/") /vnames1=!cmdend)
!LET !vnamescopy=!vnames1
!DO !cnt !in (!vnames)  
!LET !cnt1 = !HEAD(!vnamescopy)
!LET !vnamescopy=!TAIL(!vnamescopy)
variable labels !concat("I",!cnt) !cnt1.
!doend
!enddefine.
!relabel vnames=1 2 3/vnames1="James" "Paul" "Mary".

The one thing that is not working though is that I want to use a macro that contains all of my variable labels and insert it in this macro:

DEFINE !list1()
"James"
"Paul"
"Mary"
!ENDDEFINE.

so that I would invoke the macro as follows: !relabel vnames=1 2 3/vnames1=!list1.

That does not work though ...?

Any ideas?
Thanks!
Sven

I know that using macros for variable lists can be a bit tricky.  One of my colleagues wrote a little document a few years ago on some of the problems he encountered.  I don't think it addresses your problem specifically, but here it is anyway:

   http://www.angelfire.com/wv/bwhomedir/spss/macros_for_var_lists.txt


I've found that quotes can be a bit finicky in macros too.  One thing you could try is changing this line:

  variable labels !concat("I",!cnt) !cnt1.

to this:

  variable labels !concat("I",!cnt) !QUOTE(!cnt1).

To call it, then:

!relabel vnames=1 2 3/vnames1=James Paul Mary.

Or with the !list macro:

DEFINE !list1() James Paul Mary
!ENDDEFINE.

!relabel vnames=1 2 3/vnames1=!list1.

It probably won't make any difference, but that's what I'd try for starters.

David Marso...if you're reading this, please step in and straighten us out!  ;-)

[Bruce Weaver]

ksven wrote

Hi Bruce -
thanks for the suggestion. The !quote command works.

Substituting the var list with the macro still does not work though ... this is the error message I get:

(....

!doend
3339 M>  !doend
!enddefine.
3340 M>   !enddefine.
!relabel vnames=1 2 3/vnames1=!list1.
3341 M>  
3342 M>  .
3343 M>  variable labels I1 ''.
3344 M>  variable labels I2 ''.
3345 M>  variable labels I3 ''.
3346 M>  .
3347 M>  .
3348 M>  .
3349 M>  .
3350 M>  .
3351 M>  .
3352 M>  .
3353 M>  James Mary John

>Error # 1.  Command name: James
>The first word in the line is not recognized as an SPSS command.
>This command not executed.

3354 M>   .

(...)


I read through the link you gave me and changed the syntax of my macro generating the variable list, but got the same error message independent of the changes. Not sure what the problem is (?)

Best,
Sven

The following works perfectly on my PC (version 17.0.3, XP Professional).

new file.
dataset close all.

data list list / i1 i2 i3 (3f1.0).
begin data
1 2 3
4 5 6
end data.

descrip i1 to i3.

define !relabel (vnames=!charend ("/") /vnames1=!cmdend)
!LET !vnamescopy=!vnames1
!DO !cnt !in (!vnames)  
!LET !cnt1 = !HEAD(!vnamescopy)
!LET !vnamescopy=!TAIL(!vnamescopy)
variable labels !concat("I",!cnt) !QUOTE(!cnt1).
!doend
!enddefine.

DEFINE !list1() James Paul Mary
!ENDDEFINE.

set mprint on.
!relabel vnames=1 2 3/vnames1=!list1.
set mprint off.

descrip i1 to i3.

Here is the relevant output:

set mprint on.
!relabel vnames=1 2 3/vnames1=!list1.
 854  0 M>  
 855  0 M>  .
 856  0 M>  variable labels I1 'James'.
 857  0 M>  variable labels I2 'Paul'.
 858  0 M>  variable labels I3 'Mary'.
 859  0 M>  .
set mprint off.
 860  0 M>  set mprint off.

[ksven]

Hm.

I guess my problem is that I am invoking the macro generating the variable list ("!list1) by executing an external syntax file and for some reason that does not work (directly writing it out as you did does):
Is there some obvious mistake in here? Can you save the syntax to a separate file and try to include it as I did?
Thanks!
Sven

Include FILE='P:\list1.sps'.
3656 M>  Include FILE='P:\list1.sps'.
3656  DEFINE !list1()
3656 M>  DEFINE
3657 M>    !list1()
3658  James Mary John
3658 M>  James Mary John
3659 M>    !ENDDEFINE
3660   !ENDDEFINE.
3660 M>  
3662 M>  * End of INCLUDE nesting level 01
3663  * End of INCLUDE nesting level 01.
3663 M>  

3664 M>  
!relabel vnames=1 2 3/vnames1=!list1.
3665 M>  
3666 M>  .
3667 M>  variable labels I1 ''.
3668 M>  variable labels I2 ''.
3669 M>  variable labels I3 ''.
3670 M>  .
3671 M>  .
3672 M>  .
3673 M>  .
3674 M>  .
3675 M>  .
3676 M>  .
3677 M>  James Mary John

>Error # 1.  Command name: James
>The first word in the line is not recognized as an SPSS command.
>This command not executed.

3678 M>   .

[ksven]

Hm ... now it does work. Cool. I will post complete syntax for saving variable names to an external spss file and then re-using those to label other vars in the original file.
Best,
Sven

[Bruce Weaver]

ksven wrote

Hm.

I guess my problem is that I am invoking the macro generating the variable list ("!list1) by executing an external syntax file and for some reason that does not work (directly writing it out as you did does):
Is there some obvious mistake in here? Can you save the syntax to a separate file and try to include it as I did?
Thanks!
Sven

Include FILE='P:\list1.sps'.
3656 M>  Include FILE='P:\list1.sps'.
3656  DEFINE !list1()
3656 M>  DEFINE
3657 M>    !list1()
3658  James Mary John
3658 M>  James Mary John
3659 M>    !ENDDEFINE
3660   !ENDDEFINE.
3660 M>  
3662 M>  * End of INCLUDE nesting level 01
3663  * End of INCLUDE nesting level 01.
3663 M>  

3664 M>  
!relabel vnames=1 2 3/vnames1=!list1.
3665 M>  
3666 M>  .
3667 M>  variable labels I1 ''.
3668 M>  variable labels I2 ''.
3669 M>  variable labels I3 ''.
3670 M>  .
3671 M>  .
3672 M>  .
3673 M>  .
3674 M>  .
3675 M>  .
3676 M>  .
3677 M>  James Mary John

>Error # 1.  Command name: James
>The first word in the line is not recognized as an SPSS command.
>This command not executed.

3678 M>   .

INCLUDE files must adhere to "batch syntax rules".  From the Help files:

"Each command must start at the beginning of a new line (no blank spaces before the start of the command), and continuation lines must be indented at least one space. If you want to indent new commands, you can use a plus sign, dash, or period as the first character at the start of the line and then indent the actual command. The period at the end of the command is optional. This setting is compatible with the syntax rules for command files included with the INCLUDE command."

So I suspect the problem is you are defining your macro like this:

DEFINE !list1()
James
Paul
Mary
!ENDDEFINE.

Try this:

DEFINE !list1() James Paul Mary
!ENDDEFINE.

or this (one blank space before each name):

DEFINE !list1()
 James
 Paul
 Mary
!ENDDEFINE.

Both of these work via INCLUDE FILE.

[Roberts, Michael]

Good Afternoon List,

I am having a hard time getting ver. 17.0.3 to extract data from an Oracle database using queries that worked with v.17.0.1.  Has anyone experienced this sort of thing?

After making several attempts, I reviewed the syntax generated by SPSS and noted that the criteria for my selection instead of giving the variable name actually uses "null"(?)  For example, if my selection criteria is 'year=2004' in the dialogue box, in the actual syntax, the "year" is replaced with "null", so that the Where clause submitted to the server actually looks like "null=2004".

If this is a bug specific to v17.0.3, does anyone know of a fix, or a work around?  I am now wondering how much of the actual query criteria is actually being incorrectly reproduced and whether the data returned are reliable(?)

Any thoughts would be greatly appreciated!

TIA

Mike

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[SPSS Support-2]

Hi Mike,

I've opened a support case and someone will be contacting you directly.

David Nichols
SPSS, an IBM Company

From:	"Roberts, Michael" <[hidden email]>
To:	[hidden email]
Date:	03/11/2010 02:38 PM
Subject:	SPSS v17 patch 3 and query to Oracle failure
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

---

Good Afternoon List,

I am having a hard time getting ver. 17.0.3 to extract data from an Oracle database using queries that worked with v.17.0.1.  Has anyone experienced this sort of thing?

After making several attempts, I reviewed the syntax generated by SPSS and noted that the criteria for my selection instead of giving the variable name actually uses "null"(?)  For example, if my selection criteria is 'year=2004' in the dialogue box, in the actual syntax, the "year" is replaced with "null", so that the Where clause submitted to the server actually looks like "null=2004".

If this is a bug specific to v17.0.3, does anyone know of a fix, or a work around?  I am now wondering how much of the actual query criteria is actually being incorrectly reproduced and whether the data returned are reliable(?)

Any thoughts would be greatly appreciated!

TIA

Mike

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Roberts, Michael]

David (and anyone else who may have pondered my question),

 

Thank you for looking into this.  If this will help at all, I have the SPSS OEM ODBC32 v5.00 driver for Oracle set up to access the Oracle database, and until I used the third patch, there was no problem with accessing the database.   

 

Sincerely

Michael

 

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of SPSS Support
Sent: Friday, March 12, 2010 2:00 PM
To: [hidden email]
Subject: Re: SPSS v17 patch 3 and query to Oracle failure

 

Hi Mike,

I've opened a support case and someone will be contacting you directly.

David Nichols
SPSS, an IBM Company

From:	"Roberts, Michael" <[hidden email]>
To:	[hidden email]
Date:	03/11/2010 02:38 PM
Subject:	SPSS v17 patch 3 and query to Oracle failure
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

 

---

Good Afternoon List,

I am having a hard time getting ver. 17.0.3 to extract data from an Oracle database using queries that worked with v.17.0.1.  Has anyone experienced this sort of thing?

After making several attempts, I reviewed the syntax generated by SPSS and noted that the criteria for my selection instead of giving the variable name actually uses "null"(?)  For example, if my selection criteria is 'year=2004' in the dialogue box, in the actual syntax, the "year" is replaced with "null", so that the Where clause submitted to the server actually looks like "null=2004".

If this is a bug specific to v17.0.3, does anyone know of a fix, or a work around?  I am now wondering how much of the actual query criteria is actually being incorrectly reproduced and whether the data returned are reliable(?)

Any thoughts would be greatly appreciated!

TIA

Mike

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD