Fwd: Exporting SPSS data in same excel file with data and labels

classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

Fwd: Exporting SPSS data in same excel file with data and labels

venkatesh gandi

Hi Listers,

I am using SPSS24.

I am aiming to exporting SPSS data into Data & labels in same excel workbook. 

As of now I am exporting them as separately by using "SAVE TRANSLATE OUTFILE",And copying them in separate sheets. Is there a way we can do it by a syntax(with out manually). I googled it and searching for a solution from last week. But i couldn't find any solution. 

Every time it is very difficult to do this manual task.Hope by you people I can have a solution. 

The expected solution can be in either SPSS or Python is fine.

Thanks in advance,
Venkatesh

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

Re: Exporting SPSS data in same excel file with data and labels

venkatesh gandi
Hi All,

Can some one please help me on this.

Thanks,
Venkatesh

On Tue, May 16, 2017 at 12:21 AM, venkatesh gandi <[hidden email]> wrote:

Hi Listers,

I am using SPSS24.

I am aiming to exporting SPSS data into Data & labels in same excel workbook. 

As of now I am exporting them as separately by using "SAVE TRANSLATE OUTFILE",And copying them in separate sheets. Is there a way we can do it by a syntax(with out manually). I googled it and searching for a solution from last week. But i couldn't find any solution. 

Every time it is very difficult to do this manual task.Hope by you people I can have a solution. 

The expected solution can be in either SPSS or Python is fine.

Thanks in advance,
Venkatesh


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

Re: Exporting SPSS data in same excel file with data and labels

PRogman
Maybe you should present an example of your input data and how you want it to end up?

venkatesh gandi wrote
Hi All,

Can some one please help me on this.

Thanks,
Venkatesh

On Tue, May 16, 2017 at 12:21 AM, venkatesh gandi <
[hidden email]> wrote:

>
> Hi Listers,
>
> I am using SPSS24.
>
> I am aiming to exporting SPSS data into Data & labels in same excel
> workbook.
>
> As of now I am exporting them as separately by using "SAVE TRANSLATE
> OUTFILE",And copying them in separate sheets. Is there a way we can do it
> by a syntax(with out manually). I googled it and searching for a solution
> from last week. But i couldn't find any solution.
>
> Every time it is very difficult to do this manual task.Hope by you people
> I can have a solution.
>
> The expected solution can be in either SPSS or Python is fine.
>
> Thanks in advance,
> Venkatesh
>
>

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

Re: Exporting SPSS data in same excel file with data and labels

Jon Peck
In reply to this post by venkatesh gandi
I am not clear on exactly what you want, but there is a Basic script that might be suitable.  It exports one or more tables from the Viewer to Excel with the choice of separate Excel files or all tables in one file.  It can export all tables, all visible tables, or selected tables.

It does not export from the Data Editor, but if the dataset size is modest, using SUMMARIZE (Analyze > Reports > Case Summaries would make a table of the data.

If you need to do separate DE exports, you can modify the code in this script to just use the part that merges the separate Excel files.

The script is available from the SPSS Community website at
but I can send it to you directly if you can't locate it.

On Wed, May 17, 2017 at 7:31 AM, venkatesh gandi <[hidden email]> wrote:
Hi All,

Can some one please help me on this.

Thanks,
Venkatesh

On Tue, May 16, 2017 at 12:21 AM, venkatesh gandi <[hidden email]> wrote:

Hi Listers,

I am using SPSS24.

I am aiming to exporting SPSS data into Data & labels in same excel workbook. 

As of now I am exporting them as separately by using "SAVE TRANSLATE OUTFILE",And copying them in separate sheets. Is there a way we can do it by a syntax(with out manually). I googled it and searching for a solution from last week. But i couldn't find any solution. 

Every time it is very difficult to do this manual task.Hope by you people I can have a solution. 

The expected solution can be in either SPSS or Python is fine.

Thanks in advance,
Venkatesh


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

Re: Exporting SPSS data in same excel file with data and labels

venkatesh gandi
Hi Jon,

Thanks for the reply.

Listers,Pardon me if I am not clear in the mail.

I will explain clearly the situation here.

I have an SPSS data(sav). Each time I am exporting the SPSS data into excel data as "Data value" and "Data labels". using the bellow syntax.

SAVE TRANSLATE OUTFILE='Data_Values.xlsx'
  /TYPE=XLS
  /VERSION=12
  /MAP
  /REPLACE
  /FIELDNAMES
  /CELLS=VALUES.


SAVE TRANSLATE OUTFILE='Data_Labels.xlsx'
  /TYPE=XLS
  /VERSION=12
  /MAP
  /REPLACE
  /FIELDNAMES
  /CELLS=LABELS.

As this results two separate excel workbooks. But my client wants the data in the single excel file. so that the first worksheet will contains the "data values" data and the second worksheet will contains the "Data Labels". Every time I am doing this manually by copying the data.

Is there a way to obtain the resultant file by using syntax/Instead of copying the data manually can we export the data in a single file?

And I want to mention another point here that every time I am replacing the NULL values in the excel file with blanks.(The system missing values in 'sav' file results the NULL's in excel). It is taking too much of time to replacing the NULL's even I am using the 'awk' code. Can I know what are the traditional methods that listers using to replace NULL's.
  
When the data size is more, or when I am dealing with wave Data's (More files), this manual tasks are taking so much of time. Hope I am clear now.

The expected solution can be in either SPSS or Python is fine.


Thanks,
Venkatesh 

On Wed, May 17, 2017 at 10:47 PM, Jon Peck <[hidden email]> wrote:
I am not clear on exactly what you want, but there is a Basic script that might be suitable.  It exports one or more tables from the Viewer to Excel with the choice of separate Excel files or all tables in one file.  It can export all tables, all visible tables, or selected tables.

It does not export from the Data Editor, but if the dataset size is modest, using SUMMARIZE (Analyze > Reports > Case Summaries would make a table of the data.

If you need to do separate DE exports, you can modify the code in this script to just use the part that merges the separate Excel files.

The script is available from the SPSS Community website at
but I can send it to you directly if you can't locate it.

On Wed, May 17, 2017 at 7:31 AM, venkatesh gandi <[hidden email]> wrote:
Hi All,

Can some one please help me on this.

Thanks,
Venkatesh

On Tue, May 16, 2017 at 12:21 AM, venkatesh gandi <[hidden email]> wrote:

Hi Listers,

I am using SPSS24.

I am aiming to exporting SPSS data into Data & labels in same excel workbook. 

As of now I am exporting them as separately by using "SAVE TRANSLATE OUTFILE",And copying them in separate sheets. Is there a way we can do it by a syntax(with out manually). I googled it and searching for a solution from last week. But i couldn't find any solution. 

Every time it is very difficult to do this manual task.Hope by you people I can have a solution. 

The expected solution can be in either SPSS or Python is fine.

Thanks in advance,
Venkatesh


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

Re: Exporting SPSS data in same excel file with data and labels

Art Kendall
With what you have posted I get the impression you would end up with 2 columns with the same name.

ID LikeChocolate# LikeVanilla# ID LikeChocolate# LikeVanilla#

try this syntax to see if the result is like what you would want to send to Excel.

if this is the result you want,  list members may be able to suggest ways to have variable names with different suffixes.
If I recall correctly duplicating a set of variable name but with a different suffix has been done on this list.
a do repeat or loop could then create string variables with the VALUELABEL function.

data list list/ID (f3) LikeChocolate# (f1) LikeVanilla#(f1).
begin data
    1 1 1
    2 2 2
    3 1 2
    4 2 1
    5 -1 -1
end data.
value labels LikeChocolate#  LikeVanilla#
    1 'Yes'
    2 'No'
    -1 'no answer'.
missing values LikeChocolate#  LikeVanilla# (-1).
string LikeChocolate_L  LikeVanilla_L (a20).
compute LikeChocolate_L = valuelabel(LikeChocolate#).
compute LikeVanilla_L      = valuelabel(LikeVanilla#).
variable labels
    LikeChocolate#     'Liking for chocolate as a numeric value'
    LikeChocolate_L 'Liking for chocolate as a string'
    LikeVanilla#     'Liking for Vanilla as a numeric value'
    LikeVanilla_L 'Liking for Vanilla as a string'.
list.
Art Kendall
Social Research Consultants
Reply | Threaded
Open this post in threaded view
|

Re: Exporting SPSS data in same excel file with data and labels

Jon Peck
I sent Venkatish a variant of a SaxBasic script that may suffice.
On Wed, May 17, 2017 at 3:15 PM Art Kendall <[hidden email]> wrote:
With what you have posted I get the impression you would end up with 2

columns with the same name.



ID LikeChocolate# LikeVanilla# ID LikeChocolate# LikeVanilla#



try this syntax to see if the result is like what you would want to send to

Excel.



if this is the result you want,  list members may be able to suggest ways to

have variable names with different suffixes.

If I recall correctly duplicating a set of variable name but with a

different suffix has been done on this list.

a do repeat or loop could then create string variables with the VALUELABEL

function.



data list list/ID (f3) LikeChocolate# (f1) LikeVanilla#(f1).

begin data

    1 1 1

    2 2 2

    3 1 2

    4 2 1

    5 -1 -1

end data.

value labels LikeChocolate#  LikeVanilla#

    1 'Yes'

    2 'No'

    -1 'no answer'.

missing values LikeChocolate#  LikeVanilla# (-1).

string LikeChocolate_L  LikeVanilla_L (a20).

compute LikeChocolate_L = valuelabel(LikeChocolate#).

compute LikeVanilla_L      = valuelabel(LikeVanilla#).

variable labels

    LikeChocolate#     'Liking for chocolate as a numeric value'

    LikeChocolate_L 'Liking for chocolate as a string'

    LikeVanilla#     'Liking for Vanilla as a numeric value'

    LikeVanilla_L 'Liking for Vanilla as a string'.

list.







-----

Art Kendall

Social Research Consultants

--

View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Fwd-Exporting-SPSS-data-in-same-excel-file-with-data-and-labels-tp5734155p5734176.html

Sent from the SPSSX Discussion mailing list archive at 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

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

Re: Exporting SPSS data in same excel file with data and labels

Art Kendall
Okay Jon.   I did not want to create a solution until I saw what the OP really wanted.

I wonder whether the OP's client was just going to eyeball the data or try some further manipulation outside SPSS.  If the latter is the situation it is possible the client would be better served by supplying the final desired display.
Art Kendall
Social Research Consultants
Reply | Threaded
Open this post in threaded view
|

Re: Exporting SPSS data in same excel file with data and labels

Rick Oliver
In reply to this post by venkatesh gandi

If the result you want is one sheet with values and another sheet in the same workbook with labels, and you are using version 24, use the EXCELOPTIONS subcommand to specify different sheet names on each SAVE TRANSLATE command. The EXCELOPTIONS subcommand is new in version 24.

 

Sent from Mail for Windows 10

 

From: [hidden email]
Sent: Wednesday, May 17, 2017 1:36 PM
To: [hidden email]
Subject: Re: Exporting SPSS data in same excel file with data and labels

 

Hi Jon,

 

Thanks for the reply.

 

Listers,Pardon me if I am not clear in the mail.

 

I will explain clearly the situation here.

 

I have an SPSS data(sav). Each time I am exporting the SPSS data into excel data as "Data value" and "Data labels". using the bellow syntax.

 

SAVE TRANSLATE OUTFILE='Data_Values.xlsx'

  /TYPE=XLS

  /VERSION=12

  /MAP

  /REPLACE

  /FIELDNAMES

  /CELLS=VALUES.

 

 

SAVE TRANSLATE OUTFILE='Data_Labels.xlsx'

  /TYPE=XLS

  /VERSION=12

  /MAP

  /REPLACE

  /FIELDNAMES

  /CELLS=LABELS.

 

As this results two separate excel workbooks. But my client wants the data in the single excel file. so that the first worksheet will contains the "data values" data and the second worksheet will contains the "Data Labels". Every time I am doing this manually by copying the data.

 

Is there a way to obtain the resultant file by using syntax/Instead of copying the data manually can we export the data in a single file?

 

And I want to mention another point here that every time I am replacing the NULL values in the excel file with blanks.(The system missing values in 'sav' file results the NULL's in excel). It is taking too much of time to replacing the NULL's even I am using the 'awk' code. Can I know what are the traditional methods that listers using to replace NULL's.

  

When the data size is more, or when I am dealing with wave Data's (More files), this manual tasks are taking so much of time. Hope I am clear now.

 

The expected solution can be in either SPSS or Python is fine.

 

 

Thanks,

Venkatesh 

 

On Wed, May 17, 2017 at 10:47 PM, Jon Peck <[hidden email]> wrote:

I am not clear on exactly what you want, but there is a Basic script that might be suitable.  It exports one or more tables from the Viewer to Excel with the choice of separate Excel files or all tables in one file.  It can export all tables, all visible tables, or selected tables.

 

It does not export from the Data Editor, but if the dataset size is modest, using SUMMARIZE (Analyze > Reports > Case Summaries would make a table of the data.

 

If you need to do separate DE exports, you can modify the code in this script to just use the part that merges the separate Excel files.

 

The script is available from the SPSS Community website at

but I can send it to you directly if you can't locate it.

 

On Wed, May 17, 2017 at 7:31 AM, venkatesh gandi <[hidden email]> wrote:

Hi All,

 

Can some one please help me on this.

 

Thanks,

Venkatesh

 

On Tue, May 16, 2017 at 12:21 AM, venkatesh gandi <[hidden email]> wrote:

 

Hi Listers,

 

I am using SPSS24.

 

I am aiming to exporting SPSS data into Data & labels in same excel workbook. 

 

As of now I am exporting them as separately by using "SAVE TRANSLATE OUTFILE",And copying them in separate sheets. Is there a way we can do it by a syntax(with out manually). I googled it and searching for a solution from last week. But i couldn't find any solution. 

 

Every time it is very difficult to do this manual task.Hope by you people I can have a solution. 

 

The expected solution can be in either SPSS or Python is fine.

 

Thanks in advance,

Venkatesh

 

 

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

 

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

Re: Exporting SPSS data in same excel file with data and labels

venkatesh gandi
Hi Jon and Rick,

A ton of thanks for both of you for providing the solution. It saves a lot of time in the file preparation process. Now I have to look for problem with replacing NULL's in excel file. If I found a solution for that also it was the BANG moment for me :) . Listers, if possible please share your methods that you are following for replacing the NULL's.(of course we can replace in excel it self but when there are large number of NULL's it is taking more time).

Thanks once again,
Venkatesh

On Thu, May 18, 2017 at 3:04 AM, Rick Oliver <[hidden email]> wrote:

If the result you want is one sheet with values and another sheet in the same workbook with labels, and you are using version 24, use the EXCELOPTIONS subcommand to specify different sheet names on each SAVE TRANSLATE command. The EXCELOPTIONS subcommand is new in version 24.

 

Sent from Mail for Windows 10

 

From: [hidden email]
Sent: Wednesday, May 17, 2017 1:36 PM
To: [hidden email]
Subject: Re: Exporting SPSS data in same excel file with data and labels

 

Hi Jon,

 

Thanks for the reply.

 

Listers,Pardon me if I am not clear in the mail.

 

I will explain clearly the situation here.

 

I have an SPSS data(sav). Each time I am exporting the SPSS data into excel data as "Data value" and "Data labels". using the bellow syntax.

 

SAVE TRANSLATE OUTFILE='Data_Values.xlsx'

  /TYPE=XLS

  /VERSION=12

  /MAP

  /REPLACE

  /FIELDNAMES

  /CELLS=VALUES.

 

 

SAVE TRANSLATE OUTFILE='Data_Labels.xlsx'

  /TYPE=XLS

  /VERSION=12

  /MAP

  /REPLACE

  /FIELDNAMES

  /CELLS=LABELS.

 

As this results two separate excel workbooks. But my client wants the data in the single excel file. so that the first worksheet will contains the "data values" data and the second worksheet will contains the "Data Labels". Every time I am doing this manually by copying the data.

 

Is there a way to obtain the resultant file by using syntax/Instead of copying the data manually can we export the data in a single file?

 

And I want to mention another point here that every time I am replacing the NULL values in the excel file with blanks.(The system missing values in 'sav' file results the NULL's in excel). It is taking too much of time to replacing the NULL's even I am using the 'awk' code. Can I know what are the traditional methods that listers using to replace NULL's.

  

When the data size is more, or when I am dealing with wave Data's (More files), this manual tasks are taking so much of time. Hope I am clear now.

 

The expected solution can be in either SPSS or Python is fine.

 

 

Thanks,

Venkatesh 

 

On Wed, May 17, 2017 at 10:47 PM, Jon Peck <[hidden email]> wrote:

I am not clear on exactly what you want, but there is a Basic script that might be suitable.  It exports one or more tables from the Viewer to Excel with the choice of separate Excel files or all tables in one file.  It can export all tables, all visible tables, or selected tables.

 

It does not export from the Data Editor, but if the dataset size is modest, using SUMMARIZE (Analyze > Reports > Case Summaries would make a table of the data.

 

If you need to do separate DE exports, you can modify the code in this script to just use the part that merges the separate Excel files.

 

The script is available from the SPSS Community website at

but I can send it to you directly if you can't locate it.

 

On Wed, May 17, 2017 at 7:31 AM, venkatesh gandi <[hidden email]> wrote:

Hi All,

 

Can some one please help me on this.

 

Thanks,

Venkatesh

 

On Tue, May 16, 2017 at 12:21 AM, venkatesh gandi <[hidden email]> wrote:

 

Hi Listers,

 

I am using SPSS24.

 

I am aiming to exporting SPSS data into Data & labels in same excel workbook. 

 

As of now I am exporting them as separately by using "SAVE TRANSLATE OUTFILE",And copying them in separate sheets. Is there a way we can do it by a syntax(with out manually). I googled it and searching for a solution from last week. But i couldn't find any solution. 

 

Every time it is very difficult to do this manual task.Hope by you people I can have a solution. 

 

The expected solution can be in either SPSS or Python is fine.

 

Thanks in advance,

Venkatesh

 

 

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

 


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

Re: Exporting SPSS data in same excel file with data and labels

Art Kendall
What kind of SPSS value becomes a NULL in EXCEL?

During the quality assurance phase, did you perhaps  checks forget to be sure that there were only user-missing values in the SPSS data file and NO system missing values?
Art Kendall
Social Research Consultants
Reply | Threaded
Open this post in threaded view
|

Re: Exporting SPSS data in same excel file with data and labels

Rick Oliver
Missing values are converted to the null error condition in Excel. There is an option to treat user-missing as valid.

On May 18, 2017 1:50 PM, "Art Kendall" <[hidden email]> wrote:
What kind of SPSS value becomes a NULL in EXCEL?

During the quality assurance phase, did you perhaps  checks forget to be
sure that there were only user-missing values in the SPSS data file and NO
system missing values?



-----
Art Kendall
Social Research Consultants
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Fwd-Exporting-SPSS-data-in-same-excel-file-with-data-and-labels-tp5734155p5734184.html
Sent from the SPSSX Discussion mailing list archive at 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
===================== 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
Reply | Threaded
Open this post in threaded view
|

Re: Exporting SPSS data in same excel file with data and labels

Art Kendall
It would seem that the reason for a variables value to be missing for cases would still be relevant.

If I recall correctly putting
MISSING VALUES ALL ().
before creating the new variables via the VALUELABEL FUNCTION, should preserve the different user missing values and labels.
Art Kendall
Social Research Consultants
Reply | Threaded
Open this post in threaded view
|

Re: Exporting SPSS data in same excel file with data and labels

Art Kendall
This example syntax still uses the correct label for the missing value even while the status is missing.
the MISSING VALUES ALL ().
would accomplish changing the original variables' status.
Just be sure not to save over the input file to this procedure.



data list list/ID (f3) LikeChocolate# (f2) LikeVanilla#(f2).
begin data
    1 1 1
    2 2 2
    3 1 2
    4 2 1
    5 -1 -1
end data.
value labels LikeChocolate#  LikeVanilla#
    1 'Yes'
    2 'No'
    -1 'no answer'.
missing values LikeChocolate#  LikeVanilla# (-1).

string LikeChocolate_L  LikeVanilla_L (a20).
compute LikeChocolate_L = valuelabel(LikeChocolate#).
compute LikeVanilla_L      = valuelabel(LikeVanilla#).
variable labels
    LikeChocolate#     'Liking for chocolate as a numeric value'
    LikeChocolate_L 'Liking for chocolate as a string'
    LikeVanilla#     'Liking for Vanilla as a numeric value'
    LikeVanilla_L 'Liking for Vanilla as a string'.
list.
Art Kendall
Social Research Consultants
Reply | Threaded
Open this post in threaded view
|

Re: Exporting SPSS data in same excel file with data and labels

venkatesh gandi
Hi Art and Rick,

Thanks for sharing your thoughts,

I believe "MISSING VALUES" function is used to declare the user-missing value for the listed variables. Just want to let you know in my data set there will be only SYSTEM missing values. And these are converting into NAN's in Excel when exported. I hope both of you are giving a solution to dealing with user missing values  Hope I am thinking in right way. if not, please correct me.

Thanks,
Venkatesh

On Fri, May 19, 2017 at 12:56 AM, Art Kendall <[hidden email]> wrote:
This example syntax still uses the correct label for the missing value even
while the status is missing.
the MISSING VALUES ALL ().
would accomplish changing the original variables' status.
Just be sure not to save over the input file to this procedure.



data list list/ID (f3) LikeChocolate# (f2) LikeVanilla#(f2).
begin data
    1 1 1
    2 2 2
    3 1 2
    4 2 1
    5 -1 -1
end data.
value labels LikeChocolate#  LikeVanilla#
    1 'Yes'
    2 'No'
    -1 'no answer'.
missing values LikeChocolate#  LikeVanilla# (-1).

string LikeChocolate_L  LikeVanilla_L (a20).
compute LikeChocolate_L = valuelabel(LikeChocolate#).
compute LikeVanilla_L      = valuelabel(LikeVanilla#).
variable labels
    LikeChocolate#     'Liking for chocolate as a numeric value'
    LikeChocolate_L 'Liking for chocolate as a string'
    LikeVanilla#     'Liking for Vanilla as a numeric value'
    LikeVanilla_L 'Liking for Vanilla as a string'.
list.



-----
Art Kendall
Social Research Consultants
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Fwd-Exporting-SPSS-data-in-same-excel-file-with-data-and-labels-tp5734155p5734187.html
Sent from the SPSSX Discussion mailing list archive at 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

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

Re: Exporting SPSS data in same excel file with data and labels

Art Kendall
Why are there system missing values in your data if you have finished cleaning and preparing your data?
System missing indicates data is missing because the software was unable to follow your instructions. When you know why the values are missing, those values should be user missing.  

When your instructions to the computer are inadequate, that is a sign that you should redraft your instructions so the system can follow them.

At each step in your data handling when system missing values are created, that is the time to redraft your syntax so that only missing values with labels indicating the reason for the value being missing is labelled.
Wen you re-run that step of course you need to check whethe the redraft of syntax was adequate.

When you have cleaned out the system missing values for one step, then you would proceed to the next step. If  system missing values are created on that step, the fact they were created on that step gives you hints on how to redraft your syntax.

If at any point all of your system missing values are there for the same reason, then all it takes is a RECODE to change sysmis to a user missing value. then draft an ADD VALUE LABELS.

Art Kendall
Social Research Consultants
Reply | Threaded
Open this post in threaded view
|

Re: Exporting SPSS data in same excel file with data and labels

Rick Oliver
In reply to this post by venkatesh gandi
As far as I know, there is no way to prevent the direct Excel export from converting sysmis to #NULL! in Excel. I would prefer an option to export sysmis as blanks in Excel, but there are two options for dealing with the issue. The most obvious is to globally search and replace #NULL! in Excel. The other is to export the file in CSV format instead of Excel format and then open the CSV file in Excel, since  -- but then you are back to manually combining files into the same workbook, unless you use something like a Python script to write the CSV files to separate sheets in the same Excel workbook.

On Fri, May 19, 2017 at 6:44 AM, venkatesh gandi <[hidden email]> wrote:
Hi Art and Rick,

Thanks for sharing your thoughts,

I believe "MISSING VALUES" function is used to declare the user-missing value for the listed variables. Just want to let you know in my data set there will be only SYSTEM missing values. And these are converting into NAN's in Excel when exported. I hope both of you are giving a solution to dealing with user missing values  Hope I am thinking in right way. if not, please correct me.

Thanks,
Venkatesh

On Fri, May 19, 2017 at 12:56 AM, Art Kendall <[hidden email]> wrote:
This example syntax still uses the correct label for the missing value even
while the status is missing.
the MISSING VALUES ALL ().
would accomplish changing the original variables' status.
Just be sure not to save over the input file to this procedure.



data list list/ID (f3) LikeChocolate# (f2) LikeVanilla#(f2).
begin data
    1 1 1
    2 2 2
    3 1 2
    4 2 1
    5 -1 -1
end data.
value labels LikeChocolate#  LikeVanilla#
    1 'Yes'
    2 'No'
    -1 'no answer'.
missing values LikeChocolate#  LikeVanilla# (-1).

string LikeChocolate_L  LikeVanilla_L (a20).
compute LikeChocolate_L = valuelabel(LikeChocolate#).
compute LikeVanilla_L      = valuelabel(LikeVanilla#).
variable labels
    LikeChocolate#     'Liking for chocolate as a numeric value'
    LikeChocolate_L 'Liking for chocolate as a string'
    LikeVanilla#     'Liking for Vanilla as a numeric value'
    LikeVanilla_L 'Liking for Vanilla as a string'.
list.



-----
Art Kendall
Social Research Consultants
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Fwd-Exporting-SPSS-data-in-same-excel-file-with-data-and-labels-tp5734155p5734187.html
Sent from the SPSSX Discussion mailing list archive at 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

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

Re: Exporting SPSS data in same excel file with data and labels

Jon Peck
While null is the closest thing in Excel to sysmis, I can see why consumers of the Excel file might just want blank entries.

I have modified the SaxBasic script that I sent yesterday so that after it merges the two files, it does a search on nulls and replaces them with a blank.  Usage is the same as before via the SCRIPT command, but the file name is different.

The updated file is not attached, since the list will kill that.  (I sent it directly to a few people.)  If anyone else wants to see it, send me a note.  The script is not a thing of beauty, but it should do the job.

On Fri, May 19, 2017 at 1:38 PM, Jon Peck <[hidden email]> wrote:
While null is the closest thing in Excel to sysmis, I can see why consumers of the Excel file might just want blank entries.

I have modified the SaxBasic script that I sent yesterday so that after it merges the two files, it does a search on nulls and replaces them with a blank.  Usage is the same as before via the SCRIPT command, but the file name is different.

The updated file is attached, but the X list might kill that.  If anyone not on the direct email address wants to see it, send me a note.  The script is not a thing of beauty, but it should do the job.

On Fri, May 19, 2017 at 7:25 AM, Rick Oliver <[hidden email]> wrote:
As far as I know, there is no way to prevent the direct Excel export from converting sysmis to #NULL! in Excel. I would prefer an option to export sysmis as blanks in Excel, but there are two options for dealing with the issue. The most obvious is to globally search and replace #NULL! in Excel. The other is to export the file in CSV format instead of Excel format and then open the CSV file in Excel, since  -- but then you are back to manually combining files into the same workbook, unless you use something like a Python script to write the CSV files to separate sheets in the same Excel workbook.

On Fri, May 19, 2017 at 6:44 AM, venkatesh gandi <[hidden email]> wrote:
Hi Art and Rick,

Thanks for sharing your thoughts,

I believe "MISSING VALUES" function is used to declare the user-missing value for the listed variables. Just want to let you know in my data set there will be only SYSTEM missing values. And these are converting into NAN's in Excel when exported. I hope both of you are giving a solution to dealing with user missing values  Hope I am thinking in right way. if not, please correct me.

Thanks,
Venkatesh

On Fri, May 19, 2017 at 12:56 AM, Art Kendall <[hidden email]> wrote:
This example syntax still uses the correct label for the missing value even
while the status is missing.
the MISSING VALUES ALL ().
would accomplish changing the original variables' status.
Just be sure not to save over the input file to this procedure.



data list list/ID (f3) LikeChocolate# (f2) LikeVanilla#(f2).
begin data
    1 1 1
    2 2 2
    3 1 2
    4 2 1
    5 -1 -1
end data.
value labels LikeChocolate#  LikeVanilla#
    1 'Yes'
    2 'No'
    -1 'no answer'.
missing values LikeChocolate#  LikeVanilla# (-1).

string LikeChocolate_L  LikeVanilla_L (a20).
compute LikeChocolate_L = valuelabel(LikeChocolate#).
compute LikeVanilla_L      = valuelabel(LikeVanilla#).
variable labels
    LikeChocolate#     'Liking for chocolate as a numeric value'
    LikeChocolate_L 'Liking for chocolate as a string'
    LikeVanilla#     'Liking for Vanilla as a numeric value'
    LikeVanilla_L 'Liking for Vanilla as a string'.
list.



-----
Art Kendall
Social Research Consultants
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Fwd-Exporting-SPSS-data-in-same-excel-file-with-data-and-labels-tp5734155p5734187.html
Sent from the SPSSX Discussion mailing list archive at 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

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



--
Jon K Peck
[hidden email]




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

Re: Exporting SPSS data in same excel file with data and labels

venkatesh gandi
Hi All,

Just want to let you the script is working fine. And the problem is solved. Many thanks to Jon for providing solution and thanks for every one for sharing your thoughts/solutions.

Thanks,
Venkatesh

On Sat, May 20, 2017 at 1:12 AM, Jon Peck <[hidden email]> wrote:
While null is the closest thing in Excel to sysmis, I can see why consumers of the Excel file might just want blank entries.

I have modified the SaxBasic script that I sent yesterday so that after it merges the two files, it does a search on nulls and replaces them with a blank.  Usage is the same as before via the SCRIPT command, but the file name is different.

The updated file is not attached, since the list will kill that.  (I sent it directly to a few people.)  If anyone else wants to see it, send me a note.  The script is not a thing of beauty, but it should do the job.

On Fri, May 19, 2017 at 1:38 PM, Jon Peck <[hidden email]> wrote:
While null is the closest thing in Excel to sysmis, I can see why consumers of the Excel file might just want blank entries.

I have modified the SaxBasic script that I sent yesterday so that after it merges the two files, it does a search on nulls and replaces them with a blank.  Usage is the same as before via the SCRIPT command, but the file name is different.

The updated file is attached, but the X list might kill that.  If anyone not on the direct email address wants to see it, send me a note.  The script is not a thing of beauty, but it should do the job.

On Fri, May 19, 2017 at 7:25 AM, Rick Oliver <[hidden email]> wrote:
As far as I know, there is no way to prevent the direct Excel export from converting sysmis to #NULL! in Excel. I would prefer an option to export sysmis as blanks in Excel, but there are two options for dealing with the issue. The most obvious is to globally search and replace #NULL! in Excel. The other is to export the file in CSV format instead of Excel format and then open the CSV file in Excel, since  -- but then you are back to manually combining files into the same workbook, unless you use something like a Python script to write the CSV files to separate sheets in the same Excel workbook.

On Fri, May 19, 2017 at 6:44 AM, venkatesh gandi <[hidden email]> wrote:
Hi Art and Rick,

Thanks for sharing your thoughts,

I believe "MISSING VALUES" function is used to declare the user-missing value for the listed variables. Just want to let you know in my data set there will be only SYSTEM missing values. And these are converting into NAN's in Excel when exported. I hope both of you are giving a solution to dealing with user missing values  Hope I am thinking in right way. if not, please correct me.

Thanks,
Venkatesh

On Fri, May 19, 2017 at 12:56 AM, Art Kendall <[hidden email]> wrote:
This example syntax still uses the correct label for the missing value even
while the status is missing.
the MISSING VALUES ALL ().
would accomplish changing the original variables' status.
Just be sure not to save over the input file to this procedure.



data list list/ID (f3) LikeChocolate# (f2) LikeVanilla#(f2).
begin data
    1 1 1
    2 2 2
    3 1 2
    4 2 1
    5 -1 -1
end data.
value labels LikeChocolate#  LikeVanilla#
    1 'Yes'
    2 'No'
    -1 'no answer'.
missing values LikeChocolate#  LikeVanilla# (-1).

string LikeChocolate_L  LikeVanilla_L (a20).
compute LikeChocolate_L = valuelabel(LikeChocolate#).
compute LikeVanilla_L      = valuelabel(LikeVanilla#).
variable labels
    LikeChocolate#     'Liking for chocolate as a numeric value'
    LikeChocolate_L 'Liking for chocolate as a string'
    LikeVanilla#     'Liking for Vanilla as a numeric value'
    LikeVanilla_L 'Liking for Vanilla as a string'.
list.



-----
Art Kendall
Social Research Consultants
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Fwd-Exporting-SPSS-data-in-same-excel-file-with-data-and-labels-tp5734155p5734187.html
Sent from the SPSSX Discussion mailing list archive at 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

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



--
Jon K Peck
[hidden email]




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

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