Is it possible to create multiple sheet EXCEL workbook containing output with syntax?

[Ruben Geert van den Berg]

Dear all,
 
I'm wondering whether is it possible to create a multiple sheet EXCEL workbook containing output with SPSS syntax. Admittedly, it's not the end of the world if it's not possible but for a number of situations, it would come in quite handy.
 
TIA and kind regards to everyone!
 
Ruben van den Berg



 

---

Express yourself instantly with MSN Messenger! MSN Messenger

[Francien Berndsen]

> I'm wondering whether is it possible to create a multiple sheet EXCEL
> workbook containing output with SPSS syntax. Admittedly, it's not the end
> of the world if it's not possible but for a number of situations, it
would
> come in quite handy.

It's possible to create a multiple sheet excelfile by saving your data with
the function 'export to database'. You have the option to save the data in
a sheet with a name you choose. Unfortunately you have to save every sheet
in a different syntax. But it is possible to save those different sheets in
the same excel file.

But you want your output in a excel-file. Maybe you can save your output in
a datafile by OMS and than you can export your data with the export to
database-function.

Hope this helps.

Regards,
Francien

=====================
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

[Francien Berndsen]

Hi Ruben,

Actually, with 'export to database' I meant 'save translate', because I was
referring to the option under 'File' -> 'Export to database'. Below an
example of one of my syntaxfiles. With 'testsheet' I define the sheet where
I want my data pasted to. The example is a datafile with two variables
(testvar1 and testvar2).

SAVE TRANSLATE /TYPE=ODBC
 /CONNECT='DSN=Excel'+
 '-bestanden;DBQ=c:\test\test.xls;DriverId=790;MaxBufferSize=2048;PageTimeout=5;'
 /ENCRYPTED
 /MISSING = IGNORE
 /SQL='CREATE TABLE testsheet(testvar1 varchar(3), testvar2 number)'
 /REPLACE
 /TABLE='SPSS_TEMP'
 /KEEP=testvar1, testvar2
 /SQL='INSERT INTO testsheet(varchar1, varchar2) SELECT varchar1 varchar2
FROM SPSS_TEMP'
 /SQL='DROP TABLE SPSS_TEMP'.

You have to repeat this for other sheets.

Francien

On Wed, 3 Jun 2009 11:51:09 +0000, Ruben van den Berg
<[hidden email]> wrote:
> Dear Francien,
>
>
>
> I apologize for bothering you again with this, but where can I find this
> function? In my Command Syntax Reference, there's just one entry under
> 'Export', which is Export (SPSS 14, at home I've 15 thru 17). This
doesn't

> seem to be what I was looking for....
>
>
>
> Thanx a lot once again,
>
>
>
> Ruben van den Berg
>
> Btw I'm already using OMS to make a SAV of output and save it to EXCEL

with

> Save Translate.
>
>
>
>
>
>
>> Date: Wed, 3 Jun 2009 11:31:59 +0200
>> From: [hidden email]
>> Subject: Re: Is it possible to create multiple sheet EXCEL workbook
>> containing output with syntax?
>> To: [hidden email]
>>
>> > I'm wondering whether is it possible to create a multiple sheet EXCEL
>> > workbook containing output with SPSS syntax. Admittedly, it's not the
>> > end
>> > of the world if it's not possible but for a number of situations, it
>> would
>> > come in quite handy.
>>
>> It's possible to create a multiple sheet excelfile by saving your data
>> with
>> the function 'export to database'. You have the option to save the data
>> in
>> a sheet with a name you choose. Unfortunately you have to save every
>> sheet
>> in a different syntax. But it is possible to save those different sheets
>> in
>> the same excel file.
>>
>> But you want your output in a excel-file. Maybe you can save your output
>> in
>> a datafile by OMS and than you can export your data with the export to
>> database-function.
>>
>> Hope this helps.
>>
>> Regards,
>> Francien
>>
>> =====================
>> 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
>
> _________________________________________________________________
> See all the ways you can stay connected to friends and family
> http://www.microsoft.com/windows/windowslive/default.aspx

=====================
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

[Albert-Jan Roskam]

hi!

I wrote the Python program below a while ago. It uses the xlrd and xlwt modules. It merges single-sheet xls files (made with e.g. SAVE TRANSLATE) into one big merged xls file. Multisheet source files are numbered (ie., sheet2 of sourcefile.xls will get sheetname sourcefile2 in the final file.

Cheers!!
Albert-Jan

import xlrd, xlwt
import glob, os.path

def merge_xls (in_dir="d:/temp", out_file="d:/temp/merged_output.xls"):

    xls_files   = glob.glob(in_dir + "*.xls")
    sheet_names = [os.path.basename(v)[:-4] for v in xls_files]
    sheet_excl  = [os.path.basename(v)[:-4] for v in xls_files if len(os.path.basename(v)[:-4]) > 29]
    merged_book = xlwt.Workbook()

    if in_dir[-1:] != "/": in_dir = in_dir + "/"
    xls_files.sort()

    if xls_files:
        for k, xls_file in enumerate(xls_files):
            print "---> Processing file %s" % (xls_file)
            if len (sheet_names[k]) <= 29:
                book = xlrd.open_workbook(xls_file)
                if book.nsheets == 1:
                    ws = merged_book.add_sheet(sheet_names[k])
                    sheet = book.sheet_by_index(0)
                    for rx in range(sheet.nrows):
                        for cx in range(sheet.ncols):
                            ws.write(rx, cx, sheet.cell_value(rx, cx))
                elif book.nsheets in range(1, 100):
                    for sheetx in range(book.nsheets):
                        ws = merged_book.add_sheet(sheet_names[k]+str(sheetx+1).zfill(2))
                        sheet = book.sheet_by_index(sheetx)
                        for rx in range(sheet.nrows):
                            for cx in range(sheet.ncols):
                                ws.write(rx, cx, sheet.cell_value(rx, cx))
                else:
                    print "ERROR *** File %s has %s sheets (maximum is 99)" % (xls_file, book.nsheets)
                    raise
            else:
                print "WARNING *** File name too long: <%s.xls> (maximum is 31 chars) " % (sheet_names[k])
                print "WARNING *** File <%s.xls> was skipped." % (sheet_names[k])

        merged_book.save(out_file)

        print
        print "---> Merged xls file written to %s using the following source files: " % (out_file)
        for k, v in enumerate(sheet_names):
            if len(v) <= 29:
                print "\t", str(k+1).zfill(3), "%s.xls" % (v)
        print
        if sheet_excl:
            print "--> The following files were skipped because the file name exceeds 29 characters: "
            for k, v in enumerate(sheet_excl):
                print "\t", str(k+1).zfill(3), v
    else:
        print "NOTE *** No xls files in %s. Nothing to do" % (in_dir)

merge_xls()


--- On Wed, 6/3/09, Francien Berndsen <[hidden email]> wrote:

> From: Francien Berndsen <[hidden email]>
> Subject: Re: Is it possible to create multiple sheet EXCEL workbook              containing output with syntax?
> To: [hidden email]
> Date: Wednesday, June 3, 2009, 11:31 AM
> > I'm wondering whether is it
> possible to create a multiple sheet EXCEL
> > workbook containing output with SPSS syntax.
> Admittedly, it's not the end
> > of the world if it's not possible but for a number of
> situations, it
> would
> > come in quite handy.
>
> It's possible to create a multiple sheet excelfile by
> saving your data with
> the function 'export to database'. You have the option to
> save the data in
> a sheet with a name you choose. Unfortunately you have to
> save every sheet
> in a different syntax. But it is possible to save those
> different sheets in
> the same excel file.
>
> But you want your output in a excel-file. Maybe you can
> save your output in
> a datafile by OMS and than you can export your data with
> the export to
> database-function.
>
> Hope this helps.
>
> Regards,
> Francien
>
> =====================
> 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
>

=====================
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

[Bibel, Daniel (POL)]

I've found some syntax to generate soundex/nysiis recoding which refers
to a python module called 'trans' - however I can't seem to find it in
the spss developer central.

Any help in finding this appreciated.

=====================
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

[Peck, Jon]

The soundex and nysiis functions are in the extendedTransforms module.  They can be used directly if you write the code to get those values into an SPSS dataset, but the trans module provides a framework that makes that easier.
trans.py is also in the Downloads section of Developer Central.  If you select Single-Page View, it's the fifth from the bottom of the list.
HTH,
Jon Peck

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Bibel, Daniel
Sent: Wednesday, June 03, 2009 10:21 AM
To: [hidden email]
Subject: [SPSSX-L] where is the 'trans module'???

I've found some syntax to generate soundex/nysiis recoding which refers
to a python module called 'trans' - however I can't seem to find it in
the spss developer central.

Any help in finding this appreciated.

=====================
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

=====================
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

[Gyorgy Bea]

Hi All,

Do you know any online documentation on TURF analysis?
I would appreciate any recommendation, I couldn't find much.

Thanks very much,
Beata

[Norton, John]

Hi Beata,

 

This may be informative:  http://www.mv-research.com/knowledge/white.html.  The white paper on TURF is 9^th in the list of papers.  Also, please visit our Developer Central site at www.spss.com where you’ll find a custom built dialog box for TURF analysis (click on the specific link here: http://www.spss.com/devcentral/index.cfm?pg=downloadDet&dId=165).  Please read the description of that dialog box and the included README file so that you can be assured that you have the appropriate version of PASW Statistics and any plug-ins as required.

 

HTH,

 

John Norton

SPSS Inc.

 

---

From: SPSSX(r) Discussion [mailto:[hidden email]GA.EDU] On Behalf Of Gyorgy Bea
Sent: Thursday, June 04, 2009 8:45 AM
To: [hidden email]GA.EDU
Subject: TURF analysis

 

Hi All,

Do you know any online documentation on TURF analysis?
I would appreciate any recommendation, I couldn't find much.

Thanks very much,
Beata

 

 

[mpirritano]

Listees,

Is it not possible to use <> or ne with string variables?

For example: if (var <> "AAA") test = 1.

I could swear that this has worked for me in the past (?). I'm now
using:

If (char.index(var,"AAA")>0) test = 1.

That works. But it is very annoying that the <> or ne did not even
result in an error message. I was pulling out the very little hair that
I have left!

Thanks
Matt


Matthew Pirritano, Ph.D.
Research Analyst IV
Medical Services Initiative (MSI)
Orange County Health Care Agency
[phone]

=====================
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

[Maguin, Eugene]

Matt,

I'm 'sure' ne works because I use it. Do you get an error? If so, would you
post the syntax and the error message.

If no error, just the wrong result, would you post the syntax and a data
value that gives the wrong result using the posted syntax.

Gene Maguin


>>Is it not possible to use <> or ne with string variables?

For example: if (var <> "AAA") test = 1.

I could swear that this has worked for me in the past (?). I'm now
using:

If (char.index(var,"AAA")>0) test = 1.

That works. But it is very annoying that the <> or ne did not even
result in an error message. I was pulling out the very little hair that
I have left!

=====================
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

[Oliver, Richard]

AFAIK, this still works as it should:

data list free /stringvar (a8).
begin data
aaa AAA aAa abc aaa aaabcd
end data.
compute isequal=stringvar <> "aaa".
compute contains=index(stringvar, "aaa")=0.
list.


stringvar  isequal contains

aaa            .00      .00
AAA           1.00     1.00
aAa           1.00     1.00
abc           1.00     1.00
aaa            .00      .00
aaabcd        1.00      .00


Number of cases read:  6    Number of cases listed:  6

Note that there is a difference between a variable value being equal to a specified string and a variable value containing a specified string.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Pirritano, Matthew
Sent: Thursday, June 04, 2009 10:08 AM
To: [hidden email]
Subject: <>, ne and strings

Listees,

Is it not possible to use <> or ne with string variables?

For example: if (var <> "AAA") test = 1.

I could swear that this has worked for me in the past (?). I'm now
using:

If (char.index(var,"AAA")>0) test = 1.

That works. But it is very annoying that the <> or ne did not even
result in an error message. I was pulling out the very little hair that
I have left!

Thanks
Matt


Matthew Pirritano, Ph.D.
Research Analyst IV
Medical Services Initiative (MSI)
Orange County Health Care Agency
[phone]

=====================
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

=====================
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

[mpirritano]

The problem was that I had a single variable that I was testing for
three values. I thought that I needed an 'or' operator, because the
variable could only equal one of those variables at a time (being only
one variable), but it turned out I needed the 'and' operator. Sort of
counter intuitive, no?

Thanks for the help
matt

Matthew Pirritano, Ph.D.
Research Analyst IV
Medical Services Initiative (MSI)
Orange County Health Care Agency
[phone]


-----Original Message-----
From: Oliver, Richard [mailto:[hidden email]]
Sent: Thursday, June 04, 2009 8:18 AM
To: Pirritano, Matthew; [hidden email]
Subject: RE: <>, ne and strings

AFAIK, this still works as it should:

data list free /stringvar (a8).
begin data
aaa AAA aAa abc aaa aaabcd
end data.
compute isequal=stringvar <> "aaa".
compute contains=index(stringvar, "aaa")=0.
list.


stringvar  isequal contains

aaa            .00      .00
AAA           1.00     1.00
aAa           1.00     1.00
abc           1.00     1.00
aaa            .00      .00
aaabcd        1.00      .00


Number of cases read:  6    Number of cases listed:  6

Note that there is a difference between a variable value being equal to
a specified string and a variable value containing a specified string.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Pirritano, Matthew
Sent: Thursday, June 04, 2009 10:08 AM
To: [hidden email]
Subject: <>, ne and strings

Listees,

Is it not possible to use <> or ne with string variables?

For example: if (var <> "AAA") test = 1.

I could swear that this has worked for me in the past (?). I'm now
using:

If (char.index(var,"AAA")>0) test = 1.

That works. But it is very annoying that the <> or ne did not even
result in an error message. I was pulling out the very little hair that
I have left!

Thanks
Matt


Matthew Pirritano, Ph.D.
Research Analyst IV
Medical Services Initiative (MSI)
Orange County Health Care Agency
[phone]

=====================
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

=====================
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

[Richard Ristow]

At 12:19 PM 6/4/2009, Pirritano, Matthew wrote:

The problem was that I had a single variable that I was testing for three values.

The ANY function can test whether a variable takes on one of a list of values, like this (thanks, Richard Oliver):

compute isequal=ANY(stringvar,'AAA','abc').
FORMATS isequal (F2).
list.

 
List
|-----------------------------|---------------------------|
|Output Created               |04-JUN-2009 13:10:43       |
|-----------------------------|---------------------------|
stringvar isequal

aaa           0
AAA           1
aAa           0
abc           1
aaa           0
aaabcd        0


You write,

I thought that I needed an 'or' operator, because the variable could only equal one of those variables at a time, but it turned out I needed the 'and' operator. Sort of counter intuitive, no?

Is it because you're testing for equality with one of the values, but using not equal for the test?

If you aren't using the ANY function, you could write (not tested)

COMPUTE OneOfThese =  stringvar EQ 'AAA'
                   OR stringvar EQ 'abc'
                   OR stringvar EQ 'aAa'.

and there's your OR, as you expected. But if you use not-equal comparisons, are you by chance using code like this?

COMPUTE OneOfThese =  NOT
                     (stringvar <> 'AAA'
                  AND stringvar <> 'abc'
                  AND stringvar <> 'aAa').

Remember Boolean algebra: (A.or.B) == NOT(NOT.A.and.NOT.B).

================================================
APPENDIX: Test data and code, per Richard Oliver
================================================

data list free /stringvar (a8).
begin data
aaa AAA aAa abc aaa aaabcd
end data.
compute isequal=ANY(stringvar,'AAA','abc').
FORMATS isequal (F2).
list.
===================== 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

[Gyorgy Bea]

Thank you very much for all the information regarding the TURF analysis.

I've tried to run some basic analysis, but I have a question: is it possible to do any kind of significance testing on the TURF results? Does this make sense?

Thank you,
Beata

---

From: "Norton, John" <[hidden email]>
To: [hidden email]
Sent: Thursday, June 4, 2009 5:38:50 PM
Subject: Re: TURF analysis

Hi Beata,

 

This may be informative:  http://www.mv-research.com/knowledge/white.html.  The white paper on TURF is 9^th in the list of papers.  Also, please visit our Developer Central site at www.spss.com where you’ll find a custom built dialog box for TURF analysis (click on the specific link here: http://www.spss.com/devcentral/index.cfm?pg=downloadDet&dId=165).  Please read the description of that dialog box and the included README file so that you can be assured that you have the appropriate version of PASW Statistics and any plug-ins as required.

 

HTH,

 

John Norton

SPSS Inc.

 

---

From: SPSSX(r) Discussion [mailto:[hidden email] G A.EDU] On Behalf Of G yorgy Bea
Sent: Thursday, June 04, 2009 8:45 AM
To: [hidden email] G A.EDU
Subject: TURF analysis

 

Hi All,

Do you know any online documentation on TURF analysis?
I would appreciate any recommendation, I couldn't find much.

Thanks very much,
Beata

 

 

[Peck, Jon]

TURF calculations, by searching every possible combination and selecting a few out of what is usually a huge number, make doing any conventional significance test impossible AFAIK.  And many of the examples I have seen show very small differences among the top-ranked sets, so judgment takes over at that stage.

 

HTH,

Jon Peck

 

---

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Gyorgy Bea
Sent: Monday, June 15, 2009 2:50 AM
To: [hidden email]
Subject: Re: [SPSSX-L] TURF analysis

 

Thank you very much for all the information regarding the TURF analysis.

I've tried to run some basic analysis, but I have a question: is it possible to do any kind of significance testing on the TURF results? Does this make sense?

Thank you,
Beata

 

---

From: "Norton, John" <[hidden email]>
To: [hidden email]
Sent: Thursday, June 4, 2009 5:38:50 PM
Subject: Re: TURF analysis

Hi Beata,

 

This may be informative:  http://www.mv-research.com/knowledge/white.html.  The white paper on TURF is 9^th in the list of papers.  Also, please visit our Developer Central site at www.spss.com where you’ll find a custom built dialog box for TURF analysis (click on the specific link here: http://www.spss.com/devcentral/index.cfm?pg=downloadDet&dId=165).  Please read the description of that dialog box and the included README file so that you can be assured that you have the appropriate version of PASW Statistics and any plug-ins as required.

 

HTH,

 

John Norton

SPSS Inc.

 

---

From: SPSSX(r) Discussion [mailto:[hidden email] G A.EDU] On Behalf Of G yorgy Bea
Sent: Thursday, June 04, 2009 8:45 AM
To: [hidden email] G A.EDU
Subject: TURF analysis

 

Hi All,

Do you know any online documentation on TURF analysis?
I would appreciate any recommendation, I couldn't find much.

Thanks very much,
Beata