Write Command Question

[Eugenio Grant]

Hi Guys:

 

If I do I write command on a numeric variable variable of width 5 to a
*.dat file, I get this:

 

WRITE OUTFILE="filename.dat" /

V00005 (F5.0)

EXECUTE

 

The result in my *.dat file is a 7.

 

But I need to get this 00007.

 

Actually every variable has to fill to the right every blank space with
zeros, so instead of  7 I get 00007, instead of  10 I get 00010, instead
of 100, 00100 and so on:

 

This for every variable in my database of 300 variables.

 

Any ideas???

 

Regards.

 

 

 

[Richard Ristow]

At 06:41 PM 1/10/2007, Eugenio Grant wrote:

>If I do I write command on a numeric variable variable of width 5 to a
>*.dat file, I get this:
>
>
>WRITE OUTFILE="filename.dat" /
>     V00005 (F5.0)
>EXECUTE.
>
>
>The result in my *.dat file is a 7. But I need to get this 00007.

Try changing "F5.0" to "N5", etc.

-Good luck,
  Richard

[Bob Schacht-3]

Help! As usual I'm on a tight timetable...

Anyway, I'm trying to merge two files with several hundred fields in all,
and about 300 cases.
I used the Menu system, and after sorting both databases in order on the
key field, I chose Data/Merge Files/Add variables. I set the keyfield, and
ran the merge. It seemed to work. However, when I examined the data, all of
the added variables were stripped of their values and designated as system
missing!  Why is that happening, and what can I do to make it add the cases
WITH the values?

The working file has all of the 300 cases (one case turned out to be a
duplicate, but it was not among the cases that were matched with the
external file.) The external file has about 40 cases, and there were no
duplicate key IDs. The variables to be imported are all Numeric 11, and
have appropriate non-missing values.

What am I doing wrong?

Bob

Robert M. Schacht, Ph.D. <[hidden email]>
Pacific Basin Rehabilitation Research & Training Center
1268 Young Street, Suite #204
Research Center, University of Hawaii
Honolulu, HI 96814

[Richard Ristow]

At 09:41 PM 1/10/2007, Bob Schacht wrote:

>I'm trying to merge two files with several hundred fields in all, and
>about 300 cases. I used the Menu system, and after sorting both
>databases in order on the key field, I chose Data/Merge Files/Add
>variables. I set the keyfield, and ran the merge. It seemed to work.
>However, when I examined the data, all of the added variables were
>stripped of their values and designated as system missing!
>
>The working file has all of the 300 cases (one case turned out to be a
>duplicate, but it was not among the cases that were matched with the
>external file.) The external file has about 40 cases, and there were
>no
>duplicate key IDs. The variables to be imported are all Numeric 11,
>and
>have appropriate non-missing values.

First, something really simple: have you run a procedure, or clicked
Transform > Run Pending Transformations (which is EXECUTE in the
syntax)? The two files won't be read, with the new *values* filled in,
until you do that.

If that isn't it, then: Remember, the menus work by generating and
running syntax. When what you get from the menus is mysterious, look at
the syntax; it should be printed in your output file. Post the syntax,
too; that gives us a great deal more to go on.

Now, let me see if I understand your situation:

You have two files, one with c.300 cases, one with c.40 cases. They
have a key variable in common; all other variables are different.

You load the 300-case file as the working file; the 40-case file is in
a saved file, or maybe a dataset.

When you combine then with Add Variables on the menus (which is MATCH
FILES in the syntax), it appears to work; the combined file has all
variables from both input files. But there are no values for any of the
variables in the 40-case file, including for those key values that
occur in the 40-case file. (They *will* be missing for the other 260
cases.)

That could be the problem I started off with. But, as I say, post the
syntax.

-Good luck,
  Richard

[Bob Schacht-3]

At 04:57 PM 1/10/2007, Richard Ristow wrote:
>At 09:41 PM 1/10/2007, Bob Schacht wrote:
>

[snip]

>Now, let me see if I understand your situation:
>
>You have two files, one with c.300 cases, one with c.40 cases. They have a
>key variable in common; all other variables are different.

Not "all" other variables. The one with 40 cases has about 60 variables
that the main database doesn't have. To be more precise, about 60 variables
with names that don't match names in the working file with 300 cases.

>You load the 300-case file as the working file; the 40-case file is in a
>saved file, or maybe a dataset.

Right.

>When you combine then with Add Variables on the menus (which is MATCH
>FILES in the syntax), it appears to work; the combined file has all
>variables from both input files. But there are no values for any of the
>variables in the 40-case file, including for those key values that occur
>in the 40-case file.

Actually, not quite. There are string variables and others that come
through just fine. The variables that don't come through are all alike:
they contain the numbers 1-5 (likert scale type variables).

>(They *will* be missing for the other 260 cases.)
>
>That could be the problem I started off with. But, as I say, post the syntax.

I will when I get back to my office tomorrow.
Thanks,
Bob


>-Good luck,
>  Richard

[Melissa Ives]

Be sure that you are keeping the 'correct' version of the variables that
are the same in the 2 datasets.  When you do an Add Variables (Match
Files) and both datasets have the same variable, SPSS keeps the value of
the 1st listed file.  So if the 40 record file has 'updated' values for
the variables that are in both files, be sure to list that first.  (If
you are sure that the values are identical in both files, then you can
ignore this.)

Are there any warning or error messages in your output?
What does the match files syntax look like?

Melissa

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Bob Schacht
Sent: Thursday, January 11, 2007 12:24 AM
To: [hidden email]
Subject: Re: [SPSSX-L] Merge files: Add Variables

At 04:57 PM 1/10/2007, Richard Ristow wrote:
>At 09:41 PM 1/10/2007, Bob Schacht wrote:
>

[snip]

>Now, let me see if I understand your situation:
>
>You have two files, one with c.300 cases, one with c.40 cases. They
>have a key variable in common; all other variables are different.

Not "all" other variables. The one with 40 cases has about 60 variables
that the main database doesn't have. To be more precise, about 60
variables with names that don't match names in the working file with 300
cases.

>You load the 300-case file as the working file; the 40-case file is in
>a saved file, or maybe a dataset.

Right.

>When you combine then with Add Variables on the menus (which is MATCH
>FILES in the syntax), it appears to work; the combined file has all
>variables from both input files. But there are no values for any of the

>variables in the 40-case file, including for those key values that
>occur in the 40-case file.

Actually, not quite. There are string variables and others that come
through just fine. The variables that don't come through are all alike:
they contain the numbers 1-5 (likert scale type variables).

>(They *will* be missing for the other 260 cases.)
>
>That could be the problem I started off with. But, as I say, post the
syntax.

I will when I get back to my office tomorrow.
Thanks,
Bob


>-Good luck,
>  Richard



PRIVILEGED AND CONFIDENTIAL INFORMATION
This transmittal and any attachments may contain PRIVILEGED AND
CONFIDENTIAL information and is intended only for the use of the
addressee. If you are not the designated recipient, or an employee
or agent authorized to deliver such transmittals to the designated
recipient, you are hereby notified that any dissemination,
copying or publication of this transmittal is strictly prohibited. If
you have received this transmittal in error, please notify us
immediately by replying to the sender and delete this copy from your
system. You may also call us at [phone] for assistance.

[Bob Schacht-3]

At 04:57 PM 1/10/2007, Richard Ristow wrote:

>At 09:41 PM 1/10/2007, Bob Schacht wrote:
>
>>I'm trying to merge two files with several hundred fields in all, and
>>about 300 cases. I used the Menu system, and after sorting both databases
>>in order on the key field, I chose Data/Merge Files/Add variables. I set
>>the keyfield, and ran the merge. It seemed to work. However, when I
>>examined the data, all of the added variables were stripped of their
>>values and designated as system missing!. . .
>
>First, something really simple: have you run a procedure, or clicked
>Transform > Run Pending Transformations (which is EXECUTE in the syntax)?
>The two files won't be read, with the new *values* filled in, until you do
>that.

No

>If that isn't it, then: Remember, the menus work by generating and running
>syntax. When what you get from the menus is mysterious, look at the
>syntax; it should be printed in your output file. Post the syntax, too;
>that gives us a great deal more to go on.

Posting the actual syntax would be tedious, because one of the files has
very long variable names. However, the gist of it is this, with explanatory
glosses within {curly brackets}:

GET
  FILE='C:\Documents and Settings\Owner\My Documents\1 BASIC VR File
Folder\12 Working 06'+
  ' projects\NeedStudy\Needs_Assessment_All2.sav'.
MATCH FILES /FILE=*
  /FILE='C:\Documents and Settings\Owner\My Documents\1 BASIC VR File
Folder\12 Working 06 projects\NeedStudy\ZoomerangFinalParts1+2again.sav'
  /RENAME ({List of long variable names} =  d0 d1 d2 d3 d4 d5 {...etc.} )
  /BY IDNumber
  /DROP= d0 d1 d2 d3 d4 d5 {...etc.} .
EXECUTE.


>Now, let me see if I understand your situation:
>
>You have two files, one with c.300 cases, one with c.40 cases. They have a
>key variable in common; all other variables are different.

Not *all* other variables.


>You load the 300-case file as the working file; the 40-case file is in a
>saved file, or maybe a dataset.
>
>When you combine then with Add Variables on the menus (which is MATCH
>FILES in the syntax), it appears to work; the combined file has all
>variables from both input files. But there are no values for any of the
>variables in the 40-case file, including for those key values that occur
>in the 40-case file. (They *will* be missing for the other 260 cases.)

"any of the variables" is partly wrong because some string fields copied
correctly. What copied as missing were all coded 1-5 as likert scales.


>That could be the problem I started off with. But, as I say, post the syntax.

Does the syntax posted above help any?

Thanks,
Bob

Robert M. Schacht, Ph.D. <[hidden email]>
Pacific Basin Rehabilitation Research & Training Center
1268 Young Street, Suite #204
Research Center, University of Hawaii
Honolulu, HI 96814

[Richard Ristow]

At 04:35 PM 1/11/2007, Bob Schacht wrote:

>>>I'm trying to merge two files with several hundred fields in all,
>>>and about 300 cases. I used the Menu system, Data/Merge Files/Add
>>>variables. [The merge] seemed to work. However, all of the added
>>>variables were stripped of their values and designated as system
>>>missing!. . .

I suggested,

>>Remember, the menus work by generating and running syntax. When what
>>you get from the menus is mysterious, look at the syntax. Post the
>>syntax, too;
>>that gives us a great deal more to go on.
>
>Posting the actual syntax would be tedious, because one of the files
>has very long variable names. However, the gist of it is this, with
>explanatory glosses within {curly brackets}. [I've reformatted
>some-WRR]:
>
>GET
>   FILE='C:\Documents and Settings\Owner\My Documents' +
>          '\1 BASIC VR File Folder'           +
>          '\12 Working 06 projects\NeedStudy' +
>          '\Needs_Assessment_All2.sav'.
>MATCH FILES /FILE=*
>  /FILE='C:\Documents and Settings\Owner\My Documents' +
>          '\1 BASIC VR File Folder'           +
>          '\12 Working 06 projects\NeedStudy' +
>          '\ZoomerangFinalParts1+2again.sav'
>  /RENAME ({List of long variable names}
>         =  d0 d1 d2 d3 d4 d5 {...etc.} )
>  /BY IDNumber
>  /DROP= d0 d1 d2 d3 d4 d5 {...etc.} .
>EXECUTE.
>
>>You have two files, one with c.300 cases, one with c.40 cases. They
>>have a key variable in common; all other variables are different.
>
>Not *all* other variables.

OK; there are some common variables you have to drop from one of the
two files. (I see you're dropping from the shorter, on-disk file; and
the syntax is the kind the menu generates, to work around the
deficiencies of KEEP/DROP in MATCH FILES.)

>>The 300-case file is the working file. When you combine [with the
>>shorter file] with Add Variables on the menus (which is MATCH FILES
>>in the syntax), the combined file has all variables from both input
>>files. But there are no values for any of the variables in the
>>40-case file, including for those key values that occur in the
>>40-case file.
>
>"any of the variables" is partly wrong because some string fields
>copied correctly. What copied as missing were all coded 1-5 as likert
>scales.

"Some" string fields, or all of the string fields? And were any of the
numeric variables copied correctly? I gather not, from your earlier
postings.

Boy, I sure don't see the problem. I wonder if anything would show up,
if I looked at your data dictionaries? Anyway, below is a demo run.
It's SPSS draft output, release 14.0.1, and it uses datasets, rather
than scratch disk files.. The MATCH FILES is clicked up from the menus.
The code seems to do the right thing. Does this tell you anything
illuminating?

("The more obscure the observed behavior, the simpler is the underlying
problem." -User Services Group, Brown University Computer Center,
1970s. I'm looking forward to finding the simplicity in this one.)

* ...................................................          .
DATASET ACTIVATE Rcrd_40.
LIST.

List
|-----------------------------|---------------------------|
|Output Created               |12-JAN-2007 01:05:30       |
|-----------------------------|---------------------------|
KEY SHR_STR1 SHR_NUM1 B_STR1   B_NUM1 B_NUM2

  02 Mary         2    Helium      5      7
  03 Ellen        3    Lithium     5      3
  04 Susan        2    Carbon      3      2

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


DATASET ACTIVATE Rcrd_300.
LIST.

List
|-----------------------------|---------------------------|
|Output Created               |12-JAN-2007 01:05:30       |
|-----------------------------|---------------------------|
KEY A_STR1   A_NUM1 SHR_STR1 SHR_NUM1

  01 Alpha       3   Pat          7
  02 Beta        5   Mike         2
  03 Gamma       7   Pete         3
  04 Delta       2   Joe          2
  05 Epsilon     4   Dave         4

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


*  When clicking up the MATCH FILES,             .
*  -  Match on key variable; select KEY          .
*  -  Take SHR_STR1 from '*' file                .
*  -  Take SHR_NUM1 from '+' file                .
*  -  Rename SHR_STR1 from '+'= file -> S40_STR1 .

MATCH FILES /FILE=*
  /RENAME (SHR_NUM1 = d0)
  /FILE='Rcrd_40'
  /RENAME  SHR_STR1=S40_STR1
  /BY KEY
  /DROP= d0.

LIST.

List
|-----------------------------|---------------------------|
|Output Created               |12-JAN-2007 01:08:30       |
|-----------------------------|---------------------------|
KEY A_STR1   A_NUM1 SHR_STR1 S40_STR1 SHR_NUM1 B_STR1   B_NUM1 B_NUM2

  01 Alpha       3   Pat                   .                .      .
  02 Beta        5   Mike     Mary         2    Helium      5      7
  03 Gamma       7   Pete     Ellen        3    Lithium     5      3
  04 Delta       2   Joe      Susan        2    Carbon      3      2
  05 Epsilon     4   Dave                  .                .      .

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

+++++++++++++++++++
APPENDIX: Test data
+++++++++++++++++++
* ............   Test data               ............          .
*  Base file - "300 records"                      .
DATA LIST FREE
    /KEY (N2) A_STR1   (A8) A_NUM1 (F2)
              SHR_STR1 (A8) SHR_NUM1 (F2).
BEGIN DATA
1  Alpha      3     Pat    7
2  Beta       5     Mike   2
3  Gamma      7     Pete   3
4  Delta      2     Joe    2
5  Epsilon    4     Dave   4
END DATA.
FORMATS        KEY(N2).
VARIABLE WIDTH KEY(4).
VARIABLE WIDTH A_STR1 TO SHR_NUM1 (8).
DATASET NAME Rcrd_300.

Dataset Name
|-----------------------------|---------------------------|
|Output Created               |12-JAN-2007 01:05:29       |
|-----------------------------|---------------------------|

DATASET COPY Save_300.

Dataset Copy
|-----------------------------|---------------------------|
|Output Created               |12-JAN-2007 01:05:29       |
|-----------------------------|---------------------------|

*  Merged file  - "40 records"                    .
DATA LIST FREE
    /KEY (N2)
              SHR_STR1 (A8) SHR_NUM1        (F2)
              B_STR1   (A8) B_NUM1   B_NUM2 (2F2).
BEGIN DATA
2      Mary    2  Helium   5  7
3      Ellen   3  Lithium  5  3
4      Susan   2  Carbon   3  2
END DATA.
FORMATS        KEY(N2).
VARIABLE WIDTH KEY(4).
VARIABLE WIDTH SHR_STR1 TO B_NUM2 (8).
DATASET NAME Rcrd_40.

Dataset Name
|-----------------------------|---------------------------|
|Output Created               |12-JAN-2007 01:05:30       |
|-----------------------------|---------------------------|

[Bob Schacht-3]

At 08:16 PM 1/11/2007, Richard Ristow wrote:
>At 04:35 PM 1/11/2007, Bob Schacht wrote:
>
>>>>I'm trying to merge two files with several hundred fields in all, and
>>>>about 300 cases. I used the Menu system, Data/Merge Files/Add
>>>>variables. [The merge] seemed to work. However, all of the added
>>>>variables were stripped of their values and designated as system missing!. . .

Richard,
Thanks for hanging in there with me on this. I seem to have solved the
problem, and owe you (and anyone else, if interested) an answer.

While waiting for an answer, I just kept hammering at the problem, doing it
over and over. In the process, I found out several possibly relevant things:
    * My ID Number was not as unique as I thought it was. Two pairs of
cases had the same ID number through  clerical errors. These were not cases
that were matched to cases in the external file. SPSS informed me of these
errors (one run at a time).
    * It is possible that on one of the runs I did, I failed to sort *both*
files on the key ID field
    * I narrowed the scope of the variables I needed to 'import' to only
those that I needed immediately.
It is possible that some combination of these resulted in import of the
variables into the data structure but not into the database. At any rate,
when I finally made sure of sorting both databases, and modifying the ID
numbers so that they were truly unique, and narrowing the number of
variables I was dealing with, and voila! Everything went where it was
supposed to go. I apologize for not being more careful in my previous work.

Bob in HI

[Mark A Davenport MADAVENP]

The two primary (and easily forgotten) steps in a match merge: sorting and removing dups.  Be aware that only the keyed table requires unique identifiers?   

***************************************************************************************************************************************************************
Mark A. Davenport Ph.D.
Senior Research Analyst
Office of Institutional Research
The University of North Carolina at Greensboro
336.256.0395
[hidden email]

'An approximate answer to the right question is worth a good deal more than an exact answer to an approximate question.' --a paraphrase of J. W. Tukey (1962)

-----"SPSSX(r) Discussion" <[hidden email]> wrote: -----

To: [hidden email]
From: Bob Schacht <[hidden email]>
Sent by: "SPSSX(r) Discussion" <[hidden email]>
Date: 01/12/2007 09:27PM
Subject: Re: Merge files: Add Variables

At 08:16 PM 1/11/2007, Richard Ristow wrote:
>At 04:35 PM 1/11/2007, Bob Schacht wrote:
>
>>>>I'm trying to merge two files with several hundred fields in all, and
>>>>about 300 cases. I used the Menu system, Data/Merge Files/Add
>>>>variables. [The merge] seemed to work. However, all of the added
>>>>variables were stripped of their values and designated as system missing!. . .

Richard,
Thanks for hanging in there with me on this. I seem to have solved the
problem, and owe you (and anyone else, if interested) an answer.

While waiting for an answer, I just kept hammering at the problem, doing it
over and over. In the process, I found out several possibly relevant things:
   * My ID Number was not as unique as I thought it was. Two pairs of
cases had the same ID number through  clerical errors. These were not cases
that were matched to cases in the external file. SPSS informed me of these
errors (one run at a time).
   * It is possible that on one of the runs I did, I failed to sort *both*
files on the key ID field
   * I narrowed the scope of the variables I needed to 'import' to only
those that I needed immediately.
It is possible that some combination of these resulted in import of the
variables into the data structure but not into the database. At any rate,
when I finally made sure of sorting both databases, and modifying the ID
numbers so that they were truly unique, and narrowing the number of
variables I was dealing with, and voila! Everything went where it was
supposed to go. I apologize for not being more careful in my previous work.

Bob in HI