SPSSX Discussion

Exporting data to many excel sheets in one workbook based on the same condition

Classic

List

Threaded

4 messages Options

Boreak Silk

Exporting data to many excel sheets in one workbook based on the same condition

Dear All,

I have a file with two dozen variables Var1, Var2, Var3, Var4, Var5, X1, X2, …, X20 and I wanted to export

Var1, Var2, Var3, Var4, Var5, X1 to an excel sheet if X1>100

Var1, Var2, Var3, Var4, Var5, X2 to another excel sheet if X2>100

……………………..

Var1, Var2, Var3, Var4, Var5, X1 to an excel sheet if X20>100

I used below syntax but I have to do it 20 times and had to work with 20 separate excel files.

Any advice how to improve below syntax?

USE ALL.

TEMPORARY.

SELECT IF (X1 >=100).

SAVE TRANSLATE OUTFILE='C:\Temp\X1.xls'

/TYPE=XLS

/VERSION=12

/MAP

/REPLACE

/FIELDNAMES

/CELLS=VALUES

/KEEP = Var1 Var2 Var3 Var4 Var5 X1.

EXECUTE.

Thanks in advance.

Boreak

This email is intended solely for the named addressee.
If you are not the addressee indicated please delete it immediately.

Jignesh Sutar

Re: Exporting data to many excel sheets in one workbook based on the same condition

Hi Boreak,

To achieve this you would have to wrap the code up in a macro (one way of doing it). See DEFINE/ENDDEFINE in the command syntax reference as in initial starting point.

The structure of your macro would work something like this (untested):
(You could have the macro iterate through 1 to 20 and prefix with an "X" to represent the variable names, take a look at the !CONCAT function within DEFINE. Saves you from type X1 X2 X3 ect in the macro call)

define !ExportLoop (vars=!cmdend)
!do !v !in (!vars)
temp.
select if (!v>=100).
freq !v.
!doend
!enddefine.

!ExportLoop vars=X1 X2 X3.

Hope this helps,
Jignesh

On 19 June 2013 07:07, Boreak Silk <[hidden email]> wrote:

Dear All,

I have a file with two dozen variables Var1, Var2, Var3, Var4, Var5, X1, X2, …, X20 and I wanted to export

Var1, Var2, Var3, Var4, Var5, X1 to an excel sheet if X1>100
Var1, Var2, Var3, Var4, Var5, X2 to another excel sheet if X2>100

……………………..
Var1, Var2, Var3, Var4, Var5, X1 to an excel sheet if X20>100

I used below syntax but I have to do it 20 times and had to work with 20 separate excel files.

Any advice how to improve below syntax?

USE ALL.
TEMPORARY.

SELECT IF (X1 >=100).
SAVE TRANSLATE OUTFILE='C:\Temp\X1.xls'

/TYPE=XLS
/VERSION=12

/MAP
/REPLACE

/FIELDNAMES
/CELLS=VALUES

/KEEP = Var1 Var2 Var3 Var4 Var5 X1.
EXECUTE.

Thanks in advance.

Boreak

This email is intended solely for the named addressee.
If you are not the addressee indicated please delete it immediately.

Albert-Jan Roskam

Re: Exporting data to many excel sheets in one workbook based on the same condition

In reply to this post by Boreak Silk

Hello,

One could also use ODBC for this and Python so generate the code for each sheet:

FILE HANDLE xlsfile /NAME="%temp%/somefile.xls".

BEGIN PROGRAM.
import spss
filehandles = {f[0]: f[1] for f in spss.GetFileHandles()}
xlsfile = filehandles["xlsfile"]
cmd = """\

TEMPORARY.

SELECT IF (%(var)s >=100).

SAVE TRANSLATE
/CONNECT='DSN=Excel Files;DBQ=%(xlsfile)s' +
';DriverId=790;MaxBufferSize=2048;PageTimeout=5;'
/TABLE=%(var)s
/TYPE=ODBC
/REPLACE.
"""
varlist = ['v%d' % i for i in range(1, 21)]
spss.Submit([cmd % locals() for var in varlist])
END PROGRAM.

Regards,
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a
fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: Boreak Silk <[hidden email]>
To: [hidden email]
Sent: Wednesday, June 19, 2013 8:07 AM
Subject: [SPSSX-L] Exporting data to many excel sheets in one workbook based on the same condition

Dear All,

I have a file with two dozen variables Var1, Var2, Var3, Var4, Var5, X1, X2, …, X20 and I wanted to export
Var1, Var2, Var3, Var4, Var5, X1 to an excel sheet if X1>100
Var1, Var2, Var3, Var4, Var5, X2 to another excel sheet if X2>100
……………………..
Var1, Var2, Var3, Var4, Var5, X1 to an excel sheet if X20>100

I used below syntax but I have to do it 20 times and had to work with 20 separate excel files.
Any advice how to improve below syntax?

USE ALL.
TEMPORARY.
SELECT IF (X1 >=100).
SAVE TRANSLATE OUTFILE='C:\Temp\X1.xls'
/TYPE=XLS
/VERSION=12
/MAP
/REPLACE
/FIELDNAMES
/CELLS=VALUES
/KEEP = Var1 Var2 Var3 Var4 Var5 X1.
EXECUTE.

Thanks in advance.

Boreak

This email is intended solely for the named addressee.
If you are not the addressee indicated please delete it immediately.

Boreak Silk

Re: Exporting data to many excel sheets in one workbook based on the same condition

Hi Albert-Jan and Jignesh,

Thank you both for your help.

Boreak

From: Albert-Jan Roskam [mailto:[hidden email]]
Sent: Saturday, 22 June 2013 8:00 PM
To: Boreak Silk; [hidden email]
Subject: Re: [SPSSX-L] Exporting data to many excel sheets in one workbook based on the same condition

TEMPORARY.

SELECT IF (%(var)s >=100).

Regards,
Albert-Jan

From: Boreak Silk <[hidden email]>
To: [hidden email]
Sent: Wednesday, June 19, 2013 8:07 AM
Subject: [SPSSX-L] Exporting data to many excel sheets in one workbook based on the same condition

Dear All,

I have a file with two dozen variables Var1, Var2, Var3, Var4, Var5, X1, X2, …, X20 and I wanted to export

Var1, Var2, Var3, Var4, Var5, X1 to an excel sheet if X1>100

Var1, Var2, Var3, Var4, Var5, X2 to another excel sheet if X2>100

……………………..

Var1, Var2, Var3, Var4, Var5, X1 to an excel sheet if X20>100

I used below syntax but I have to do it 20 times and had to work with 20 separate excel files.

Any advice how to improve below syntax?

USE ALL.

TEMPORARY.

SELECT IF (X1 >=100).

SAVE TRANSLATE OUTFILE='C:\Temp\X1.xls'

/TYPE=XLS

/VERSION=12

/MAP

/REPLACE

/FIELDNAMES

/CELLS=VALUES

/KEEP = Var1 Var2 Var3 Var4 Var5 X1.

EXECUTE.

Thanks in advance.

Boreak

This email is intended solely for the named addressee.
If you are not the addressee indicated please delete it immediately.