SPSSX Discussion

split file. Export to Excel with split variables as filename.

classic Classic list List threaded Threaded
♦
♦
 5 messages Options
Art Kendall
Reply | Threaded
Open this post in threaded view
♦
♦
|

split file. Export to Excel with split variables as filename.

Art Kendall
I have been looking at SPSSX-l on my phone but have a hard time reading the
results. I have the impression there are SPSSINC ways to do this.

Obviously, I could do many times
temporary.
select if.
save translate ...

I have a file like this. (untested)

data list list /Floor (a1) group(a3) LastName (a25) FirstName (a25).
begin data
1 ABC "SMITH" "John"
1 ABC "JONES Jr." "William (Bill)"
1 DEF "PUBLIC" "John Q."
1 DEF "ANTHONY" "Susan B."
2 DEF "WASHINGTON" "George (G.W.)"
2 DEF "ADAMS" "John"
2 ABC "SKINNER" "Leonard"
2 ABC "BUNYON" "Paul"
1 ABC "SMITH" "Mary"
1 ABC "JONES Jr." "Anne"
1 DEF "PUBLIC" "Suzy Q."
1 DEF "ANTHONY" "Marc"
2 DEF "WASHINGTON" "Martha"
2 DEF "ADAMS" "Abigail"
2 ABC "SKINNER" "Zelda"
2 ABC "BUNYON" "Pearl"
end data.


What my friends are trying to do is.

Sort cases by Floor group.
Split file by Floor group.
----
(the actual task will have more files to output)
----
then export to  Excel files:

1 ABC.xlsx
1 DEF.xlsx
2 ABC.xlsx
2 DEF.xlsx









-----
Art Kendall
Social Research Consultants
--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

=====================
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
Art Kendall
Social Research Consultants
Jon Peck
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: split file. Export to Excel with split variables as filename.

Jon Peck
SPSSINC SPLIT DATASET can make sav files using the split values.  Then SPSSINC PROCESS FILES can iterate over them and execute syntax to convert them.  The PROCESS FILES doc has an example.

On Wed, Jan 17, 2018 at 2:17 PM Art Kendall <[hidden email]> wrote:
I have been looking at SPSSX-l on my phone but have a hard time reading the
results. I have the impression there are SPSSINC ways to do this.

Obviously, I could do many times
temporary.
select if.
save translate ...

I have a file like this. (untested)

data list list /Floor (a1) group(a3) LastName (a25) FirstName (a25).
begin data
1 ABC "SMITH" "John"
1 ABC "JONES Jr." "William (Bill)"
1 DEF "PUBLIC" "John Q."
1 DEF "ANTHONY" "Susan B."
2 DEF "WASHINGTON" "George (G.W.)"
2 DEF "ADAMS" "John"
2 ABC "SKINNER" "Leonard"
2 ABC "BUNYON" "Paul"
1 ABC "SMITH" "Mary"
1 ABC "JONES Jr." "Anne"
1 DEF "PUBLIC" "Suzy Q."
1 DEF "ANTHONY" "Marc"
2 DEF "WASHINGTON" "Martha"
2 DEF "ADAMS" "Abigail"
2 ABC "SKINNER" "Zelda"
2 ABC "BUNYON" "Pearl"
end data.


What my friends are trying to do is.

Sort cases by Floor group.
Split file by Floor group.
----
(the actual task will have more files to output)
----
then export to  Excel files:

1 ABC.xlsx
1 DEF.xlsx
2 ABC.xlsx
2 DEF.xlsx









-----
Art Kendall
Social Research Consultants
--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

=====================
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
--
Jon K Peck
[hidden email]

===================== 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
Art Kendall
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: split file. Export to Excel with split variables as filename.

Art Kendall
Thank you Jon.
I am back to my computer this morning.
Below is an example snippet of syntax that worked when I tested it.

*Note that you need to put the last block into a separate syntax file.*

* Encoding: UTF-8.
* Example syntax to save subsets of cases to different Excel spreadsheets.
* Create some data.
data list list /Floor (a1) group(a3) LastName (a25) FirstName (a25).
begin data
    1 ABC "SMITH" "John"
    1 ABC "JONES Jr." "William (Bill)"
    1 DEF "PUBLIC" "John Q."
    1 DEF "ANTHONY" "Susan B."
    2 DEF "WASHINGTON" "George (G.W.)"
    2 DEF "ADAMS" "John"
    2 ABC "SKINNER" "Leonard"
    2 ABC "BUNYON" "Paul"
    1 ABC "SMITH" "Mary"
    1 ABC "JONES Jr." "Anne"
    1 DEF "PUBLIC" "Suzy Q."
    1 DEF "ANTHONY" "Marc"
    2 DEF "WASHINGTON" "Martha"
    2 DEF "ADAMS" "Abigail"
    2 ABC "SKINNER" "Zelda"
    2 ABC "BUNYON" "Pearl"
end data.
DATASET NAME ExampleData.
Sort cases by Floor group LastName FirstName.

* save subsets of cases to SAV files.

SPSSINC SPLIT DATASET SPLITVAR = Floor group
    /OUTPUT
    DIRECTORY ="E:"
    DELETECONTENTS=NO
    MAKETEMPDIR=NO
    /OPTIONS NAMES =VALUES
    PRINTLIST=YES
    FILELIST="E:\FILELIST".

* Bring back in each SAV with a subset of cases then save it as an Excel
XLSX spreadsheet.

define !input () !quote(!concat(!unquote(!eval(!JOB_OUTPUTDATADIR)),
    !unquote(!eval(!JOB_DATAFILEROOT)),
".sav")) !enddefine.
define !out () !quote(!concat(!unquote(!eval(!JOB_OUTPUTDATADIR)),
    !unquote(!eval(!JOB_DATAFILEROOT)),
".xlsx")) !enddefine.

SPSSINC PROCESS FILES
    SYNTAX ="E:\SPSS sav to xlsx.sps"
    INPUTDATA="E:\"
    OUTPUTDATADIR="E:\"
    LOGFILE="E:\LOG.TXT"
    CLOSEDATA=YES.

*---------------------------------.
* Example syntax for option SPSSINC PROCESS FILES above.
* put this is the appropriate location.
* then run the syntax above the dashed line.
GET
    FILE=!input.

SAVE TRANSLATE OUTFILE=!out
    /TYPE=XLS
    /VERSION=12
    /MAP
    /FIELDNAMES VALUE=NAMES
    /CELLS=VALUES
    /REPLACE.








-----
Art Kendall
Social Research Consultants
--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

=====================
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
Art Kendall
Social Research Consultants
Jon Peck
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: split file. Export to Excel with split variables as filename.

Jon Peck
Not too painful :-)

Unlike SPLIT FILES, there is no need to sort the files for SPSSINC SPLIT DATASET.

On Thu, Jan 18, 2018 at 7:52 AM, Art Kendall <[hidden email]> wrote:
Thank you Jon.
I am back to my computer this morning.
Below is an example snippet of syntax that worked when I tested it.

*Note that you need to put the last block into a separate syntax file.*

* Encoding: UTF-8.
* Example syntax to save subsets of cases to different Excel spreadsheets.
* Create some data.
data list list /Floor (a1) group(a3) LastName (a25) FirstName (a25).
begin data
    1 ABC "SMITH" "John"
    1 ABC "JONES Jr." "William (Bill)"
    1 DEF "PUBLIC" "John Q."
    1 DEF "ANTHONY" "Susan B."
    2 DEF "WASHINGTON" "George (G.W.)"
    2 DEF "ADAMS" "John"
    2 ABC "SKINNER" "Leonard"
    2 ABC "BUNYON" "Paul"
    1 ABC "SMITH" "Mary"
    1 ABC "JONES Jr." "Anne"
    1 DEF "PUBLIC" "Suzy Q."
    1 DEF "ANTHONY" "Marc"
    2 DEF "WASHINGTON" "Martha"
    2 DEF "ADAMS" "Abigail"
    2 ABC "SKINNER" "Zelda"
    2 ABC "BUNYON" "Pearl"
end data.
DATASET NAME ExampleData.
Sort cases by Floor group LastName FirstName.

* save subsets of cases to SAV files.

SPSSINC SPLIT DATASET SPLITVAR = Floor group
    /OUTPUT
    DIRECTORY ="E:"
    DELETECONTENTS=NO
    MAKETEMPDIR=NO
    /OPTIONS NAMES =VALUES
    PRINTLIST=YES
    FILELIST="E:\FILELIST".

* Bring back in each SAV with a subset of cases then save it as an Excel
XLSX spreadsheet.

define !input () !quote(!concat(!unquote(!eval(!JOB_OUTPUTDATADIR)),
    !unquote(!eval(!JOB_DATAFILEROOT)),
".sav")) !enddefine.
define !out () !quote(!concat(!unquote(!eval(!JOB_OUTPUTDATADIR)),
    !unquote(!eval(!JOB_DATAFILEROOT)),
".xlsx")) !enddefine.

SPSSINC PROCESS FILES
    SYNTAX ="E:\SPSS sav to xlsx.sps"
    INPUTDATA="E:\"
    OUTPUTDATADIR="E:\"
    LOGFILE="E:\LOG.TXT"
    CLOSEDATA=YES.

*---------------------------------.
* Example syntax for option SPSSINC PROCESS FILES above.
* put this is the appropriate location.
* then run the syntax above the dashed line.
GET
    FILE=!input.

SAVE TRANSLATE OUTFILE=!out
    /TYPE=XLS
    /VERSION=12
    /MAP
    /FIELDNAMES VALUE=NAMES
    /CELLS=VALUES
    /REPLACE.








-----
Art Kendall
Social Research Consultants
--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

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



--
Jon K Peck
[hidden email]

===================== 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
Art Kendall
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: split file. Export to Excel with split variables as filename.

Art Kendall
Good clarification.

In this instance we needed Condo LastName FirstName sorted withing the
splits and could have done it in the included syntax file.



-----
Art Kendall
Social Research Consultants
--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

=====================
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
Art Kendall
Social Research Consultants
Free forum by Nabble Edit this page