Adding 1000 SPSS files
A.A.A
How can i add 1000 SPSS FILES (each file contains just one number) so
that i will end up with one SPSS file having one column and 1000 rows?
A.A.A
A.A.A
Bruce Weaver
I'm guessing the 1000 files are numbered as in some of your other
recent posts. If so, you could do something like:
1. Open the first file.
2. In a macro that loops from i = 2 to 1000:
a. generate the file name using i
b. use ADD FILES to add the data from file i to the working file
3. After the last file has been added, save the working data file.
The basic syntax for ADD FILES is:
ADD FILES
file = * /
file = "C:\MyFolder\file_i.sav" .
The * means "currently active data set".
--
Bruce Weaver
bwe...@lakeheadu.ca
www.angelfire.com/wv/bwhomedir
"When all else fails, RTFM."
A.A.A
> "When all else fails, RTFM."
Hi Bruce,
Would the syntax be something like this:
Define !Xel().
!do !i=2 !to 1000.
GET DATA
/TYPE=XLS
/FILE=!Quote(!concat('E:\simuldata1\', 'measure', !i, '.xls'))
/SHEET=name 'Sheet1'
/CELLRANGE=full
/READNAMES=off
/ASSUMEDSTRWIDTH=32767.
SAVE OUTFILE= !quote(!concat('E:\work\', 'measure', !i, '.sav')).
GET
FILE= 'E:\work\measure1.sav'.
ADD FILES
file = * /
file = "E:\work\measure_i.sav" .
SAVE OUTFILE='E:\work\measure.sav'.
!doend.
!Enddefine.
!Xel.
Note that the files named "measure" are originally in an excel format.
I have 2 problems
1- I GOT MANY ERRORS WHEN USING THIS SYNTAX AND THE OUTPUT FILE"
MEASURE" CONTAINS THE RESULTS OF THE FIRST RUN ONLY.
2-I HAVE READ IN THE SPSS HELP FILES THAT "ADD FILES combines cases
from 2 up to 50 SPSS-format data files". DOES THIS MEAN THAT I WILL
ONLY BE ABLE TO COMBINE 50 SPSS FILES?
THANKS,
Aya
Bruce Weaver
I don't remember if you said what version of SPSS you have. Assuming
it is recent enough to support the DATASET commands, try this:
Define !Xel().
* --- Open the first file and assign a dataset name --- .
GET DATA
/TYPE=XLS
/FILE= "E:\simuldata1\measure1.xls"
/SHEET=name 'Sheet1'
/CELLRANGE=full
/READNAMES=off
/ASSUMEDSTRWIDTH=32767.
DATASET NAME file_1 window = front.
* --- Loop through the remaining files, appending them to the main
file --- .
!do !i=2 !to 1000.
GET DATA
/TYPE=XLS
/FILE=!Quote(!concat('E:\simuldata1\', 'measure', !i, '.xls'))
/SHEET=name 'Sheet1'
/CELLRANGE=full
/READNAMES=off
/ASSUMEDSTRWIDTH=32767.
DATASET NAME file_i.
ADD FILES
file = 'file_1' /
file = 'file_i' .
exe.
DATASET NAME file_1 window = front.
!doend.
save outfile = "E:\simuldata1\all_runs.sav" .
dataset close all.
!Enddefine.
* --- Call the macro --- .
!Xel.
--
Bruce Weaver
bwe...@lakeheadu.ca
www.angelfire.com/wv/bwhomedir
A.A.A
>
> - Show quoted text -
Hi Bruce,
Unfortunately,it is not a recent version it is SPSS 13 and i think
this is not possible in this version.
Thanks,
Aya
Bruce Weaver
OK, then I think you have to have two loops, one to import from Excel
and save in SPSS format (.SAV); and a second to loop through and do
the merging. This works on a little example I made up. You'll have
to change the numbers and variable names.
Define !Xel().
* --- Import Excel files and save as .SAV --- .
!do !i=1 !to 3.
GET DATA
/TYPE=XLS
/FILE=!Quote(!concat('C:\Temp\', 'junk', !i, '.xls'))
/SHEET=name 'Sheet1'
/CELLRANGE=full
/READNAMES=off
/ASSUMEDSTRWIDTH=32767.
save outfile = !Quote(!concat('C:\Temp\', 'junk', !i, '.sav'))
!doend.
* --- Write file1 data to "all_files.sav" --- .
get file = "C:\Temp\junk1.sav".
save outfile = "C:\Temp\all_files.sav".
* --- Another loop to merge the files ---- .
!do !i=2 !to 3.
ADD FILES
file = 'C:\Temp\all_files.sav' /
file = !Quote(!concat('C:\Temp\', 'junk', !i, '.sav')) .
exe.
save outfile = 'C:\Temp\all_files.sav'.
!doend.
!Enddefine.
* --- Call the macro --- .
!Xel.
--
Bruce Weaver
bwe...@lakeheadu.ca
A.A.A
Hi Bruce,
The syntax works and i am able now to combine my files but i am afraid
because i read in the SPSS help files that" ADD FILES combines cases
from 2 up to 50 SPSS-format data files by concatenating or
interleaving cases" as i have 1000 files or more. Unfortunately, I do
not have these files now to try whether the ADD FILES can combine more
than 50 files or not. So, is it true that i wont be able to combine
more than 50 files?????????????
Thanks Bruce,
Aya
Bruce Weaver
> Hi Bruce,
>
> The syntax works and i am able now to combine my files but i am afraid
> because i read in the SPSS help files that" ADD FILES combines cases
> from 2 up to 50 SPSS-format data files by concatenating or
> interleaving cases" as i have 1000 files or more. Unfortunately, I do
> not have these files now to try whether the ADD FILES can combine more
> than 50 files or not. So, is it true that i wont be able to combine
> more than 50 files?????????????
> Thanks Bruce,
> Aya
I believe that is referring to the number of files you can add in one
command. E.g.,
ADD FILES
file = "C:\MyFolder\file1.sav" /
file = "C:\MyFolder\file2.sav" /
file = "C:\MyFolder\file3.sav" /
...
file = "C:\MyFolder\file50.sav" .
exe.
But in the code I posted, ADD FILES is always using only two files, so
you should not have a problem.
--
Bruce Weaver
bwe...@lakeheadu.ca
www.angelfire.com/wv/bwhomedir
A.A.A
A.A.A
> "When all else fails, RTFM."
Hi Bruce,
The following is a syntax you sent to me previously(it was a syntax
for performing multinomial logistic regression
and saving the classification overall percentage and parameter
estimates in an excel file for 10 datasets).
Here is the syntax:
Define !Xel().
OMS SELECT TABLES/IF SUBTYPE='Parameter Estimates' /DESTINATION
FORMAT=SAV
OUTFILE='E:\simuldata1\MLECOEFF.sav'.
OMS SELECT TABLES/IF SUBTYPE='classification'/DESTINATION FORMAT=SAV
OUTFILE='E:\simuldata1\MLECLASS.sav'.
!do !i=1 !to 10.
GET DATA
/TYPE=XLS
/FILE=!Quote(!concat('E:\simulationexcel\', 'datas', !i, '.xls'))
/SHEET=name 'Sheet1'
/CELLRANGE=full
/READNAMES=on
/ASSUMEDSTRWIDTH=32767.
*saving the file after droping unwanted columns, then loading the new
file.
***************************************************************************
*.
SAVE OUTFILE= !quote(!concat('E:\work\', 'datas', !i, '.sav'))
/DROP=V1 V4 V5 V6 V7 V8 V9 V10 V11 V12 V13 /COMPRESSED.
GET
FILE= !quote(!concat('E:\work\', 'datas', !i, '.sav')).
*Performing multinomial logistic regression.
*******************************************.
NOMREG
V14 (BASE=FIRST ORDER=ASCENDING) WITH @1 @2
/CRITERIA CIN(95) DELTA(0) MXITER(100) MXSTEP(5) CHKSEP(20)
LCONVERGE(0) PCONVERGE(0.000001) SINGULAR(0.00000001)
/MODEL
/STEPWISE = PIN(.05) POUT(0.1) MINEFFECT(0) RULE(SINGLE)
ENTRYMETHOD(LR) REMOVALMETHOD(LR)
/INTERCEPT =INCLUDE
/PRINT = CLASSTABLE PARAMETER SUMMARY LRT CPS STEP MFI .
!doend.
OMSEND.
GET FILE='E:\simuldata1\MLECOEFF.sav'.
SELECT IF(Var2='Intercept'or Var2='@1' or Var2='@2').
EXE.
DELETE VARIABLES Command_ Subtype_ label_ var1 Std.Error Wald dF
Sig ExpB lowerBound UpperBound.
SAVE OUTFILE = 'E:\simuldata1\MLECOEFF.sav'.
GET FILE='E:\simuldata1\MLECLASS.sav'.
SELECT IF(Var1='Overall Percentage').
EXE.
DELETE VARIABLES Command_ TO @3.
SAVE OUTFILE='E:\simuldata1\MLECLASS.sav'.
Get file='E:\simuldata1\MLECOEFF.sav'.
SAVE TRANSLATE OUTFILE='E:\simuldata1\MLECOEFF.XLS'
/TYPE=XLS
/VERSION=8
/KEEP=B
/FIELDNAMES.
Get file='E:\simuldata1\MLECLASS.sav'.
SAVE TRANSLATE OUTFILE='E:\simuldata1\MLECLASS.XLS'
/TYPE=XLS
/VERSION=8
/KEEP=ALL
/FIELDNAMES.
!Enddefine.
!Xel.
I expected the resulting file"MPCOEFF" to give me a file of 30 rows (3
rows for each dataset;one for the intercept and one for @1 and another
for@2). However, i got an excel file with 61 rows. I do not know why i
got these extra rows?
Thanks
Bruce Weaver
Well for one thing, I see you are asking for STEPWISE selection
(which is rarely a good idea--see the comments in Rich Ulrich's
Stats FAQ, which you can find via Google). That will give you two
"steps" per model, which will increase the number of rows in your
file. Second, I know that even when I have entered all variables
at once, I have seen two steps, the first one being an intercept
only model. I *think* that was for the binary logistic regression
procedure, but it may be that NOMREG does the same thing. In any
case, there should be clues in the data file (possibly in the
variables you deleted earlier) that will help you figure out
what's going on.
--
Bruce Weaver
bwe...@lakeheadu.ca
www.angelfire.com/wv/bwhomedir
JKPeck
> "When all else fails, RTFM."
Note that the OMS IF subcommand accepts an INSTANCES keyword.
INSTANCES=[LAST]
specifies that only the last instance within a procedure's output of
the particular table type selected is to be saved. This is designed
for the case when a procedure may produce several of the same table
but the last one, which is typically the final result, is the one you
want. (Which is not to say that stepwise regression is a good idea.)
HTH,
Jon Peck
Bruce Weaver
>
> Note that the OMS IF subcommand accepts an INSTANCES keyword.
> INSTANCES=[LAST]
> specifies that only the last instance within a procedure's output of
> the particular table type selected is to be saved. This is designed
> for the case when a procedure may produce several of the same table
> but the last one, which is typically the final result, is the one you
> want. (Which is not to say that stepwise regression is a good idea.)
>
> HTH,
> Jon Peck
I'd not noticed that--and it will be useful. Thanks Jon.
--
Bruce Weaver
bwe...@lakeheadu.ca
www.angelfire.com/wv/bwhomedir