File Handle or Macro Question

[Ann Flanagan]

Hello,

I have a sytax file that I am trying to run but when I do I get a warning
that "No variables are defined".  My data are 50 individual state data files
(.dat) and the syntax file begins:


*NOTE: IN FILE HANDLE, REPLACE D: WITH APPROPRIATE DRIVE LETTER  .

*NOTE: IN FILE HANDLE, REPLACE __ WITH APPROPRIATE STATE CODE    .

FILE HANDLE  MST2  /NAME='D:\DATA\M31ST2__.DAT' /LRECL=3375.

DATA LIST  FILE=MST2/



When I replace NAME='D:\DATA\M31ST2__.DAT'  with a state file name,
e.g., NAME='D:\DATA\M31ST2CA.DAT'
the program runs fine.



Can anyone help me figure out the problem?



Thanks.



Ann

[Richard Ristow]

At 07:00 PM 8/16/2007, Ann Flanagan wrote:

>I have a syntax file that I am trying to run but when I do I get a
>warning that "No variables are defined".  My data are 50 individual
>state data files (.dat) and the syntax file begins:
>
>*NOTE: IN FILE HANDLE, REPLACE D: WITH APPROPRIATE DRIVE LETTER  .
>*NOTE: IN FILE HANDLE, REPLACE __ WITH APPROPRIATE STATE CODE    .
>
>FILE HANDLE  MST2  /NAME='D:\DATA\M31ST2__.DAT' /LRECL=3375.
>DATA LIST  FILE=MST2/
>
>When I replace NAME='D:\DATA\M31ST2__.DAT'  with a state file name,
>e.g., NAME='D:\DATA\M31ST2CA.DAT' the program runs fine.
>
>Can anyone help me figure out the problem?

This may not be a 'problem', but how the syntax was intended to
operate.

A lot of public data, particularly from government sources, is
distributed as yours appears to be: as one or more data files, with
SPSS syntax files to read them, possibly providing other services such
as setting formats, variable and value labels, and missing values.

Since the source agencies can't know what directory you'll put the data
files in, most such syntax files begin with a FILE HANDLE statement,
and instructions to modify it to 'point' to where your data actually
is. Yours is like that.

In your data, in addition, there apparently is a data file for each
state; but all the records have the same format, so the same syntax
file can read any of them. The syntax file as shipped isn't set up to
read any state file, but has '__' where the state abbreviation would
be:

>*NOTE: IN FILE HANDLE, REPLACE __ WITH APPROPRIATE STATE CODE    .
>
>FILE HANDLE  MST2  /NAME='D:\DATA\M31ST2__.DAT' /LRECL=3375.

It's not intended to run as is; and if you try it, it won't work
because there's no "M31ST2__.DAT" data file. You say that

>when I replace NAME='D:\DATA\M31ST2__.DAT'  with a state file name,
>e.g., NAME='D:\DATA\M31ST2CA.DAT' the program runs fine.

That's almost certainly how it's supposed to work: you change the FILE
HANDLE to 'point' to whichever of the state files you're interested in
- California, in your example.

Does this make sense, or am I missing something?

-Best wishes to you,
  Richard

[Richard Ristow]

Apologies - this took me considerably longer than I'd hoped.
At 02:18 AM 8/17/2007, Ann Flanagan wrote, off-list:

>So...these
>>*NOTE: IN FILE HANDLE, REPLACE D: WITH APPROPRIATE DRIVE LETTER  .
>>*NOTE: IN FILE HANDLE, REPLACE __ WITH APPROPRIATE STATE CODE   .
>notes are to me or to the user.

Exactly; internal instructions for using the code, as sent.

> From how I understand what you have said, I need to read in each
> state raw data file separately?

I'm afraid so. Or, you could catenate the data files first, using a
text editor or some such; making jolly sure that there's a variable
that identifies which state a record is from.

>What then is the command for "appending" the data?

It's ADD FILES - see below. Be careful;

>>ADD FILES combines cases from 2 up to 50 SPSS-format data files by
>>concatenating or interleaving cases. (SPSS 15 Command Syntax
>>Reference p.106);

With states, you're likely over the limit of 50, with territories and
the District of Columbia. I'll skip that, but you may have to run in
two batches, and then use a last ADD FILES to combine the combined
files.

>Is there a way to write a macro such that `i' defines states and
>DATA\M31ST2`i'.DAT' would run the syntax file for each state
>automatically? [Borrowing from Stata's macro language here...]

Here we go. Tested; this is SPSS 15 draft output. (It uses datasets. On
versions before 14, replace DATASET NAME by a FILE HANDLE defining a
scratch file with that name, and then SAVE FILE to it.)

You'll see that VARIABLE LABELS and FORMATS are in a separate macro
called !Attribs. That keeps the text of macro !InState from being very
long when, as is probably true for you, the labels, formats, MISSING
VALUES, etc., are probably very long.

For contents of the test data, see end of this posting.
*  .................   Post after this point   ..................... .

DEFINE !Attribs()
VAR LABELS County "County name"
            Size   "County population".
FORMATS    Size  (COMMA7).
.
!ENDDEFINE.

DEFINE !InState (States=!CMDEND)
   !DO !State !IN (!States)
      !Let !Handle=!CONCAT('Read_',!State)
      !Let !QState=!QUOTE(!State)
      !LET  !File=!CONCAT('C:\Documents and Settings'      +
                            '\Richard\My Documents'        +
                            '\Temporary\SPSS\'             +
                         '2007-08-17 Flanagan - '          +
                         'File Handle or Macro Question'   +
                         ' - TESTDATA-',
                         !State,
                         '.txt')
       !LET !File= !QUOTE(!File).

      FILE HANDLE !Handle /NAME=!File.
      DATA LIST FIXED FILE=!Handle /
        County 01-08 (A)
        Size   10-14.
      !Attribs.
      STRING   State (A2).
      COMPUTE  State = !QUOTE(!State).
      DATASET NAME !State.
      LIST.
   !DOEND
   ADD FILES
   !DO !State !IN (!States)
       /FILE=!State
   !DOEND
   .

!ENDDEFINE.

PRESERVE.
SET MPRINT ON.
!InState States=AL AK.
  131 M>
  132 M>  .
  133 M>  FILE HANDLE Read_AL /NAME= 'C:\Documents and
Settings\Richard\My Docume
             nts\Temporary\SPSS\2007-08-17 Flanagan - File Handle or
Macro Questi
             on - TESTDATA-AL.txt'.
  134 M>  DATA LIST FIXED FILE= Read_AL / County 01-08 (A) Size 10-14.

Data List will read 1 records from C:\Documents and Settings\...
TESTDATA-AL.txt

Variable          Rec   Start     End  Format

County              1       1       8  A8
Size                1      10      14  F5.0

  135 M>
  136 M>  .
  137 M>  VAR LABELS County 'County name' Size 'County population'.
  138 M>  FORMATS Size (COMMA7)
  139 M>  .
  140 M>  STRING State (A2).
  141 M>  COMPUTE State = 'AL'.
  142 M>  DATASET NAME AL.
  143 M>  LIST.

List
|-----------------------------|---------------------------|
|Output Created               |22-AUG-2007 23:26:50       |
|-----------------------------|---------------------------|
[AL]

County      Size State

Alpha      8,557 AL
Beta       1,419 AL
Gamma      2,339 AL


Number of cases read:  3    Number of cases listed:  3


  144 M>
  145 M>  FILE HANDLE Read_AK /NAME= 'C:\Documents and
Settings\Richard\My Docume
             nts\Temporary\SPSS\2007-08-17 Flanagan - File Handle or
Macro Questi
             on - TESTDATA-AK.txt'.
  146 M>  DATA LIST FIXED FILE= Read_AK / County 01-08 (A) Size 10-14.

Data List will read 1 records from C:\Documents and Settings\...
TESTDATA-AK.txt

Variable          Rec   Start     End  Format

County              1       1       8  A8
Size                1      10      14  F5.0

  147 M>
  148 M>  .
  149 M>  VAR LABELS County 'County name' Size 'County population'.
  150 M>  FORMATS Size (COMMA7)
  151 M>  .
  152 M>  STRING State (A2).
  153 M>  COMPUTE State = 'AK'.
  154 M>  DATASET NAME AK.
  155 M>  LIST.

List
|-----------------------------|---------------------------|
|Output Created               |22-AUG-2007 23:26:50       |
|-----------------------------|---------------------------|
[AK]

County      Size State

Delta      1,442 AK
Epsilon   23,067 AK
Zeta      51,091 AK
Eta       28,319 AK
Theta     25,477 AK


Number of cases read:  5    Number of cases listed:  5


  156 M>  ADD FILES /FILE= AL /FILE= AK
  157 M>  .
RESTORE.
  158 M>  RESTORE.
LIST.

List
|-----------------------------|---------------------------|
|Output Created               |22-AUG-2007 23:26:50       |
|-----------------------------|---------------------------|
County      Size State

Alpha      8,557 AL
Beta       1,419 AL
Gamma      2,339 AL
Delta      1,442 AK
Epsilon   23,067 AK
Zeta      51,091 AK
Eta       28,319 AK
Theta     25,477 AK

Number of cases read:  8    Number of cases listed:  8

===================
APPENDIX: Test data
===================
*  .................   Test data               ..................... .

DATA LIST FIXED
   /STATE   04-05 (A)
    County  08-15 (A).
BEGIN DATA
    AL  Alpha
    AL  Beta
    AL  Gamma
    AK  Delta
    AK  Epsilon
    AK  Zeta
    AK  Eta
    AK  Theta
    AZ  Iota
    AZ  Kappa
    AZ  Lambda
    AZ  Mu
END DATA.
NUMERIC SIZE (COMMA9).
COMPUTE SIZE= TRUNC(RV.EXP(1/25E3)).
LIST.

FILE HANDLE Write_AL
  /NAME='C:\Documents and Settings\Richard\My Documents'              +
          '\Temporary\SPSS\'                                          +
        '2007-08-17 Flanagan - File Handle or Macro Question'         +
        ' - '                                                         +
        'TESTDATA-AL.TXT'.

FILE HANDLE Write_AK
  /NAME='C:\Documents and Settings\Richard\My Documents'              +
          '\Temporary\SPSS\'                                          +
        '2007-08-17 Flanagan - File Handle or Macro Question'         +
        ' - '                                                         +
        'TESTDATA-AK.TXT'.

FILE HANDLE Write_AZ
  /NAME='C:\Documents and Settings\Richard\My Documents'              +
          '\Temporary\SPSS\'                                          +
        '2007-08-17 Flanagan - File Handle or Macro Question'         +
        ' - '                                                         +
        'TESTDATA-AZ.TXT'.

DO IF   STATE EQ 'AL'.
.  PRINT /          'To AL: ' County * Size(F6).
.  WRITE OUTFILE= Write_AL /  County * Size(F6).
ELSE IF STATE EQ 'AK'.
.  PRINT /          'To AK: ' County * Size(F6).
.  WRITE OUTFILE= Write_AK  / County * Size(F6).
ELSE IF STATE EQ 'AZ'.
.  PRINT /          'To AZ: ' County * Size(F6).
.  WRITE OUTFILE= Write_AZ  / County * Size(F6).
END IF.
EXECUTE.
===================================
Original single file, listed:
List
|-----------------------------|---------------------------|
|Output Created               |22-AUG-2007 23:26:48       |
|-----------------------------|---------------------------|
STATE County        SIZE

AL    Alpha        8,557
AL    Beta         1,419
AL    Gamma        2,339
AK    Delta        1,442
AK    Epsilon     23,067
AK    Zeta        51,091
AK    Eta         28,319
AK    Theta       25,477
AZ    Iota        80,128
AZ    Kappa       27,692
AZ    Lambda      35,060
AZ    Mu           5,322

Number of cases read:  12    Number of cases listed:  12
===================================
Records written to three files:
EXECUTE.
To AL: Alpha      8557
To AL: Beta       1419
To AL: Gamma      2339
To AK: Delta      1442
To AK: Epsilon   23067
To AK: Zeta      51091
To AK: Eta       28319
To AK: Theta     25477
To AZ: Iota      80128
To AZ: Kappa     27692
To AZ: Lambda    35060
To AZ: Mu         5322

[Ann Flanagan]

Wow Richard, thank you so much.  The data are restricted and I am away from
the office so I won't have a chance to test this for a couple of days but I
can't wait.

Again, thank you, above and beyond the call of any listserv!


On 8/22/07, Richard Ristow <[hidden email]> wrote:

>
> Apologies - this took me considerably longer than I'd hoped.
> At 02:18 AM 8/17/2007, Ann Flanagan wrote, off-list:
>
> >So...these
> >>*NOTE: IN FILE HANDLE, REPLACE D: WITH APPROPRIATE DRIVE LETTER  .
> >>*NOTE: IN FILE HANDLE, REPLACE __ WITH APPROPRIATE STATE CODE   .
> >notes are to me or to the user.
>
> Exactly; internal instructions for using the code, as sent.
>
> > From how I understand what you have said, I need to read in each
> > state raw data file separately?
>
> I'm afraid so. Or, you could catenate the data files first, using a
> text editor or some such; making jolly sure that there's a variable
> that identifies which state a record is from.
>
> >What then is the command for "appending" the data?
>
> It's ADD FILES - see below. Be careful;
>
> >>ADD FILES combines cases from 2 up to 50 SPSS-format data files by
> >>concatenating or interleaving cases. (SPSS 15 Command Syntax
> >>Reference p.106);
>
> With states, you're likely over the limit of 50, with territories and
> the District of Columbia. I'll skip that, but you may have to run in
> two batches, and then use a last ADD FILES to combine the combined
> files.
>
> >Is there a way to write a macro such that `i' defines states and
> >DATA\M31ST2`i'.DAT' would run the syntax file for each state
> >automatically? [Borrowing from Stata's macro language here...]
>
> Here we go. Tested; this is SPSS 15 draft output. (It uses datasets. On
> versions before 14, replace DATASET NAME by a FILE HANDLE defining a
> scratch file with that name, and then SAVE FILE to it.)
>
> You'll see that VARIABLE LABELS and FORMATS are in a separate macro
> called !Attribs. That keeps the text of macro !InState from being very
> long when, as is probably true for you, the labels, formats, MISSING
> VALUES, etc., are probably very long.
>
> For contents of the test data, see end of this posting.
> *  .................   Post after this point   ..................... .
>
> DEFINE !Attribs()
> VAR LABELS County "County name"
>            Size   "County population".
> FORMATS    Size  (COMMA7).
> .
> !ENDDEFINE.
>
> DEFINE !InState (States=!CMDEND)
>   !DO !State !IN (!States)
>      !Let !Handle=!CONCAT('Read_',!State)
>      !Let !QState=!QUOTE(!State)
>      !LET  !File=!CONCAT('C:\Documents and Settings'      +
>                            '\Richard\My Documents'        +
>                            '\Temporary\SPSS\'             +
>                         '2007-08-17 Flanagan - '          +
>                         'File Handle or Macro Question'   +
>                         ' - TESTDATA-',
>                         !State,
>                         '.txt')
>       !LET !File= !QUOTE(!File).
>
>      FILE HANDLE !Handle /NAME=!File.
>      DATA LIST FIXED FILE=!Handle /
>        County 01-08 (A)
>        Size   10-14.
>      !Attribs.
>      STRING   State (A2).
>      COMPUTE  State = !QUOTE(!State).
>      DATASET NAME !State.
>      LIST.
>   !DOEND
>   ADD FILES
>   !DO !State !IN (!States)
>       /FILE=!State
>   !DOEND
>   .
>
> !ENDDEFINE.
>
> PRESERVE.
> SET MPRINT ON.
> !InState States=AL AK.
> 131 M>
> 132 M>  .
> 133 M>  FILE HANDLE Read_AL /NAME= 'C:\Documents and
> Settings\Richard\My Docume
>             nts\Temporary\SPSS\2007-08-17 Flanagan - File Handle or
> Macro Questi
>             on - TESTDATA-AL.txt'.
> 134 M>  DATA LIST FIXED FILE= Read_AL / County 01-08 (A) Size 10-14.
>
> Data List will read 1 records from C:\Documents and Settings\...
> TESTDATA-AL.txt
>
> Variable          Rec   Start     End  Format
>
> County              1       1       8  A8
> Size                1      10      14  F5.0
>
> 135 M>
> 136 M>  .
> 137 M>  VAR LABELS County 'County name' Size 'County population'.
> 138 M>  FORMATS Size (COMMA7)
> 139 M>  .
> 140 M>  STRING State (A2).
> 141 M>  COMPUTE State = 'AL'.
> 142 M>  DATASET NAME AL.
> 143 M>  LIST.
>
> List
> |-----------------------------|---------------------------|
> |Output Created               |22-AUG-2007 23:26:50       |
> |-----------------------------|---------------------------|
> [AL]
>
> County      Size State
>
> Alpha      8,557 AL
> Beta       1,419 AL
> Gamma      2,339 AL
>
>
> Number of cases read:  3    Number of cases listed:  3
>
>
> 144 M>
> 145 M>  FILE HANDLE Read_AK /NAME= 'C:\Documents and
> Settings\Richard\My Docume
>             nts\Temporary\SPSS\2007-08-17 Flanagan - File Handle or
> Macro Questi
>             on - TESTDATA-AK.txt'.
> 146 M>  DATA LIST FIXED FILE= Read_AK / County 01-08 (A) Size 10-14.
>
> Data List will read 1 records from C:\Documents and Settings\...
> TESTDATA-AK.txt
>
> Variable          Rec   Start     End  Format
>
> County              1       1       8  A8
> Size                1      10      14  F5.0
>
> 147 M>
> 148 M>  .
> 149 M>  VAR LABELS County 'County name' Size 'County population'.
> 150 M>  FORMATS Size (COMMA7)
> 151 M>  .
> 152 M>  STRING State (A2).
> 153 M>  COMPUTE State = 'AK'.
> 154 M>  DATASET NAME AK.
> 155 M>  LIST.
>
> List
> |-----------------------------|---------------------------|
> |Output Created               |22-AUG-2007 23:26:50       |
> |-----------------------------|---------------------------|
> [AK]
>
> County      Size State
>
> Delta      1,442 AK
> Epsilon   23,067 AK
> Zeta      51,091 AK
> Eta       28,319 AK
> Theta     25,477 AK
>
>
> Number of cases read:  5    Number of cases listed:  5
>
>
> 156 M>  ADD FILES /FILE= AL /FILE= AK
> 157 M>  .
> RESTORE.
> 158 M>  RESTORE.
> LIST.
>
> List
> |-----------------------------|---------------------------|
> |Output Created               |22-AUG-2007 23:26:50       |
> |-----------------------------|---------------------------|
> County      Size State
>
> Alpha      8,557 AL
> Beta       1,419 AL
> Gamma      2,339 AL
> Delta      1,442 AK
> Epsilon   23,067 AK
> Zeta      51,091 AK
> Eta       28,319 AK
> Theta     25,477 AK
>
> Number of cases read:  8    Number of cases listed:  8
>
> ===================
> APPENDIX: Test data
> ===================
> *  .................   Test data               ..................... .
>
> DATA LIST FIXED
>   /STATE   04-05 (A)
>    County  08-15 (A).
> BEGIN DATA
>    AL  Alpha
>    AL  Beta
>    AL  Gamma
>    AK  Delta
>    AK  Epsilon
>    AK  Zeta
>    AK  Eta
>    AK  Theta
>    AZ  Iota
>    AZ  Kappa
>    AZ  Lambda
>    AZ  Mu
> END DATA.
> NUMERIC SIZE (COMMA9).
> COMPUTE SIZE= TRUNC(RV.EXP(1/25E3)).
> LIST.
>
> FILE HANDLE Write_AL
> /NAME='C:\Documents and Settings\Richard\My Documents'              +
>          '\Temporary\SPSS\'                                          +
>        '2007-08-17 Flanagan - File Handle or Macro Question'         +
>        ' - '                                                         +
>        'TESTDATA-AL.TXT'.
>
> FILE HANDLE Write_AK
> /NAME='C:\Documents and Settings\Richard\My Documents'              +
>          '\Temporary\SPSS\'                                          +
>        '2007-08-17 Flanagan - File Handle or Macro Question'         +
>        ' - '                                                         +
>        'TESTDATA-AK.TXT'.
>
> FILE HANDLE Write_AZ
> /NAME='C:\Documents and Settings\Richard\My Documents'              +
>          '\Temporary\SPSS\'                                          +
>        '2007-08-17 Flanagan - File Handle or Macro Question'         +
>        ' - '                                                         +
>        'TESTDATA-AZ.TXT'.
>
> DO IF   STATE EQ 'AL'.
> .  PRINT /          'To AL: ' County * Size(F6).
> .  WRITE OUTFILE= Write_AL /  County * Size(F6).
> ELSE IF STATE EQ 'AK'.
> .  PRINT /          'To AK: ' County * Size(F6).
> .  WRITE OUTFILE= Write_AK  / County * Size(F6).
> ELSE IF STATE EQ 'AZ'.
> .  PRINT /          'To AZ: ' County * Size(F6).
> .  WRITE OUTFILE= Write_AZ  / County * Size(F6).
> END IF.
> EXECUTE.
> ===================================
> Original single file, listed:
> List
> |-----------------------------|---------------------------|
> |Output Created               |22-AUG-2007 23:26:48       |
> |-----------------------------|---------------------------|
> STATE County        SIZE
>
> AL    Alpha        8,557
> AL    Beta         1,419
> AL    Gamma        2,339
> AK    Delta        1,442
> AK    Epsilon     23,067
> AK    Zeta        51,091
> AK    Eta         28,319
> AK    Theta       25,477
> AZ    Iota        80,128
> AZ    Kappa       27,692
> AZ    Lambda      35,060
> AZ    Mu           5,322
>
> Number of cases read:  12    Number of cases listed:  12
> ===================================
> Records written to three files:
> EXECUTE.
> To AL: Alpha      8557
> To AL: Beta       1419
> To AL: Gamma      2339
> To AK: Delta      1442
> To AK: Epsilon   23067
> To AK: Zeta      51091
> To AK: Eta       28319
> To AK: Theta     25477
> To AZ: Iota      80128
> To AZ: Kappa     27692
> To AZ: Lambda    35060
> To AZ: Mu         5322
>
>

[Richard Ristow]

Regarding the code I previously posted:

Dear Anne,

At 07:06 PM 9/3/2007, you wrote:

>The macro you wrote is just great.  I can run it and leave for a
>couple of hours and come back.

Terrific!

>The only problem is with the ADD FILES in the end.  I get an error
>message #5146 that tells me there is file to restore.

I don't know what #5146 is, but is there a chance you're hitting the
limit of 50 files on an ADD FILES? It sounded like that might be a risk
with your data - counting territories and the District of Columbia,
there are generally more than 50 sets of state data.

If so, the work-around is to run the macro twice, once for the first
half of the states (or few enough to be under 50) and once for the
rest. Then, you can join the two resulting files with a single ADD
FILES.

>I don't know if it has anything to do with the first error message I
>get which is:  The active dataset will replace the existing dataset
>named AL (but AL remains an open dataset). The only change I made to
>the macro was to comment out the LIST command (* LIST.)

No, that's a warning message that's apparently for when you change the
active dataset without ever having used it. That's probably why I
didn't see it: the LIST command counted as a 'use'. Ignore the warning;
the code is behaving as it should, for your circumstances.

And of course you needed to comment out the LIST. It would give you
voluminous useless output in production, though it was useful for
testing.

-Cheers, and good luck again,
  Richard