SPSS to Excel Automation

[Brock-15]

Hi all,

I am hoping you can provide some insight.  While I have not attempted to
do this first-hand, I have seen that the programming language in SPSS (SAX
I believe) is extremely similar to that of the VBA used in Office
Applications.  Using Access, I have been able to generate recordsets
(queries that summarize my data) and using code, automate my results to
very specific locations on a pre-formatted Excel worksheet.  I was hoping
I could do this in SPSS.

My thought would be that I would run some syntax in code such that my
results are stored in my code, and I export the resulting table to Excel
from SPSS (or run the code entirely from Excel).  This way I can ,
datapoint by datapoint, determine where I want my results to go and in the
format that I like.

Any thoughts?

Brock

[Shauna Marie Wilhelm]

I too am trying to automat my output for a monthly report, but I have
run into the problem that if all of my categories do not show for a
given question, then when I export the SPSS output that the referenced
cells are now off for each of the categories that are not shown. This
problem cascades through out my output and I have to go through and
insert lines into the exported excel file to ensure that the referenced
cells are correctly referencing the correct output. I am not very
familiar with ACESS so I have no help for you there, but I came across a
website that had a possible solution.

http://www.spsstools.net/Syntax/ChartsTables/ShowEmptyCategoryInTables.t
xt

My problem now is that I don't have access to the Tables feature in
SPSS. Does anyone have another idea how to solve this? I don't really
want to add data to my dataset anyways. I know about adding data for the
missing responses and weighting them so that they are virtually at a
zero weight (>0.0001). Again, I don't like the idea of adding cases to
trick SPSS. Is there another way?

Shaun

-----Original Message-----
From: [hidden email] [mailto:[hidden email]]
Sent: Friday, November 03, 2006 10:37 AM
To: [hidden email]
Subject: SPSS to Excel Automation

Hi all,

I am hoping you can provide some insight.  While I have not attempted to
do this first-hand, I have seen that the programming language in SPSS
(SAX
I believe) is extremely similar to that of the VBA used in Office
Applications.  Using Access, I have been able to generate recordsets
(queries that summarize my data) and using code, automate my results to
very specific locations on a pre-formatted Excel worksheet.  I was
hoping
I could do this in SPSS.

My thought would be that I would run some syntax in code such that my
results are stored in my code, and I export the resulting table to Excel
from SPSS (or run the code entirely from Excel).  This way I can ,
datapoint by datapoint, determine where I want my results to go and in
the
format that I like.

Any thoughts?

Brock

[Hal 9000]

Shauna,

I do something like this:

(you can paste everything between the starry lines into the syntax editor).

**************** begin syntax **********************.

* sample data:  .

new file.
data list free /id v1 v2 v4.
begin data
001 1 1 2
002 2 1 1
003 2 1 3
004 2 2 2
end data.
dataset name A.

* [.....v3 is missing].

dataset copy B.
dataset activate B.

n of cases 1.
flip.
dataset name C.

set mexpand on mprint on.
define !M (Y = !charend ('/'))
!do !C !in (!Y)
dataset activate C.
compute flag = 0.
do if case_lbl = !quote(!C)
compute flag = 1.
end if.
sort cases by flag (d).
do if $casenum = 1 & flag = 1.
+ write outfile = 'zz.sps'
        /'* Var Exists - Do Nothing *.'.
else.
+ write outfile = 'zz.sps'
        /!quote(!concat('compute ', !C, ' = 0.')).
end if.
exe.
dataset activate A.
insert file = 'zz.sps'.
!doend.
!enddefine.

!M Y = v1 v2 v3 v4 /.
dataset close B.
dataset close C.

* v3 is discovered missing and computed as a blank var.

***************************** end syntax ************************************.

Best of luck!
-Gary



On 11/3/06, Shauna Marie Wilhelm <[hidden email]> wrote:

> I too am trying to automat my output for a monthly report, but I have
> run into the problem that if all of my categories do not show for a
> given question, then when I export the SPSS output that the referenced
> cells are now off for each of the categories that are not shown. This
> problem cascades through out my output and I have to go through and
> insert lines into the exported excel file to ensure that the referenced
> cells are correctly referencing the correct output. I am not very
> familiar with ACESS so I have no help for you there, but I came across a
> website that had a possible solution.
>
> http://www.spsstools.net/Syntax/ChartsTables/ShowEmptyCategoryInTables.t
> xt
>
> My problem now is that I don't have access to the Tables feature in
> SPSS. Does anyone have another idea how to solve this? I don't really
> want to add data to my dataset anyways. I know about adding data for the
> missing responses and weighting them so that they are virtually at a
> zero weight (>0.0001). Again, I don't like the idea of adding cases to
> trick SPSS. Is there another way?
>
> Shaun
>
> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]]
> Sent: Friday, November 03, 2006 10:37 AM
> To: [hidden email]
> Subject: SPSS to Excel Automation
>
> Hi all,
>
> I am hoping you can provide some insight.  While I have not attempted to
> do this first-hand, I have seen that the programming language in SPSS
> (SAX
> I believe) is extremely similar to that of the VBA used in Office
> Applications.  Using Access, I have been able to generate recordsets
> (queries that summarize my data) and using code, automate my results to
> very specific locations on a pre-formatted Excel worksheet.  I was
> hoping
> I could do this in SPSS.
>
> My thought would be that I would run some syntax in code such that my
> results are stored in my code, and I export the resulting table to Excel
> from SPSS (or run the code entirely from Excel).  This way I can ,
> datapoint by datapoint, determine where I want my results to go and in
> the
> format that I like.
>
> Any thoughts?
>
> Brock
>

[Hal 9000]

as I said....something "Like" that...

Try this instead (tested a okay):


* conditionally compute missing variable.

* sample data:  .

new file.
data list free /id v1 v2 v4.
begin data
001 1 1 2
002 2 1 1
003 2 1 3
004 2 2 2
end data.
dataset name A.

* [v3 is missing].

dataset copy B.
dataset activate B.

n of cases 1.
flip.
dataset name C.

set mexpand on mprint on.
define !M (Y = !charend ('/'))
!do !C !in (!Y)
dataset activate C.
compute flag = 0.
do if case_lbl = !quote(!C).
compute flag = 1.
end if.
sort cases by flag (d).
do if $casenum = 1 & flag = 1.
+ write outfile = 'zz.sps'
        /'* Var Exists - Do Nothing *.'.
else if $casenum = 1 & flag = 0.
+ write outfile = 'zz.sps'
        /!quote(!concat('compute ', !C, ' = 0.')).
end if.
exe.
dataset activate A.
insert file = 'zz.sps'.
!doend.
!enddefine.

!M Y = v1 v2 v3 v4 /.
dataset close B.
dataset close C.
exe.

* v3 is discovered missing and computed as a blank var.