LOOP + GENLINMIXED + SAVE OUTPUT in different excel sheets

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

LOOP + GENLINMIXED + SAVE OUTPUT in different excel sheets

Alejandro González Heras

Hi there,

 

I have two questions, one regarding the LOOP and one curiosity (at the end) about GENLINMIXED

 

So I have many target variables in a GENLINMIXED model I am running. First I created the loop, which works fine, and then I tried to save each analysis in one different excel sheet

 

The output export (see below) is supposed to save 6 times in the excel workbook in 6 different excel sheets. I skipped the excel sheet name because it would be overwritten. But I have to be missing something, since the code below saves the output 6 times, and as a result I have all the info (the 6 models) but in the same excel sheet (because the output is cumulative, if I were to write in syntax to close the output viewer, it only saves the last iteration).

 

DEFINE macdef (!POS !CHAREND('/'))

!DO !i !IN (!1)

    GENLINMIXED

              /DATA_STRUCTURE SUBJECTS=ID_Egos_nivel2

              /FIELDS TARGET=!i TRIALS=NONE OFFSET=NONE

              /TARGET_OPTIONS REFERENCE = 1 DISTRIBUTION=BINOMIAL LINK=LOGIT

              /FIXED  EFFECTS=

                  EDAD_EVENTO

                  cat.socioeco.origen.EGO

                  estudis_PARE_agrupat

                  estudis_ego_agrupat

                  Sexo_varon

                USE_INTERCEPT=TRUE

              /RANDOM USE_INTERCEPT=TRUE SUBJECTS=ID_Egos_nivel2 COVARIANCE_TYPE=VARIANCE_COMPONENTS SOLUTION=FALSE

              /BUILD_OPTIONS TARGET_CATEGORY_ORDER=ASCENDING INPUTS_CATEGORY_ORDER=ASCENDING MAX_ITERATIONS=400

                CONFIDENCE_LEVEL=95 DF_METHOD=RESIDUAL COVB=MODEL PCONVERGE=0.000001(ABSOLUTE) SCORING=0

                SINGULAR=0.000000000001

              /EMMEANS_OPTIONS SCALE=ORIGINAL PADJUST=LSD.

     OUTPUT EXPORT

                      /CONTENTS  EXPORT=VISIBLE  LAYERS=VISIBLE  MODELVIEWS=VISIBLE

                      /XLSX  DOCUMENTFILE='C:\Users\34656\Desktop\GLMM.xlsx'

                 OPERATION=CREATESHEET

                 LOCATION=LASTROW      NOTESCAPTIONS=YES.

!DOEND

!ENDDEFINE.

macdef

                    BUSQUEDA_INFORMAL

                    BUSQUEDA_ONLINE

                    BUSQUEDA_FORMAL                    

                    INSERCION_INFORMAL

                    INSERCION_ONLINE

                    INSERCION_FORMAL  /.

 

 

The genlinmixed curiosity:

 

If the SUBJECTS keyworkd for the RANDOM subcommand is optional according to IBM (though IBM here does use it), what does it do to not specify SUBJECTS in RANDOM subcommand? I have been somehow answered to this already, something about the SPSS config and the longitudinal analysis, but I can’t figure out what it does (the results vary significantly if I don’t specify it). And I have also seen in some places that if you don’t specify that keywork, that is one of the reasons for the “non-positive definite Hesssian matrix” error to appear. This is only a curiosity, I have figured out already that I have to specify it, I would just like to understand it.

 

 

All the best,

Alejandro

===================== 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: LOOP + GENLINMIXED + SAVE OUTPUT in different excel sheets

spss.giesel@yahoo.de
Hi, Alejandro,

Q1: try sth. like this:

* -----------------------------------------------------------------------------------------------.
DEFINE macdef (!POS !CHAREND('/'))
...
!DO !i !IN (!1)
  !LET !sheet = !QUOTE(!i) /* define macro variable */
OUTPUT EXPORT
  /CONTENTS  EXPORT=VISIBLE  LAYERS=VISIBLE  MODELVIEWS=VISIBLE
  /XLSX  DOCUMENTFILE='C:\Users\34656\Desktop\GLMM.xlsx'
 OPERATION=CREATESHEET
 SHEET = !sheet /* use macro variable*/
 LOCATION=LASTROW      NOTESCAPTIONS=YES.
!DOEND
!ENDDEFINE.
* -----------------------------------------------------------------------------------------------.

GL,
Mario Giesel
Munich, Germany

Am Dienstag, 2. März 2021, 12:39:20 MEZ hat Alejandro González Heras <[hidden email]> Folgendes geschrieben:


Hi there,

 

I have two questions, one regarding the LOOP and one curiosity (at the end) about GENLINMIXED

 

So I have many target variables in a GENLINMIXED model I am running. First I created the loop, which works fine, and then I tried to save each analysis in one different excel sheet

 

The output export (see below) is supposed to save 6 times in the excel workbook in 6 different excel sheets. I skipped the excel sheet name because it would be overwritten. But I have to be missing something, since the code below saves the output 6 times, and as a result I have all the info (the 6 models) but in the same excel sheet (because the output is cumulative, if I were to write in syntax to close the output viewer, it only saves the last iteration).

 

DEFINE macdef (!POS !CHAREND('/'))

!DO !i !IN (!1)

    GENLINMIXED

              /DATA_STRUCTURE SUBJECTS=ID_Egos_nivel2

              /FIELDS TARGET=!i TRIALS=NONE OFFSET=NONE

              /TARGET_OPTIONS REFERENCE = 1 DISTRIBUTION=BINOMIAL LINK=LOGIT

              /FIXED  EFFECTS=

                  EDAD_EVENTO

                  cat.socioeco.origen.EGO

                  estudis_PARE_agrupat

                  estudis_ego_agrupat

                  Sexo_varon

                USE_INTERCEPT=TRUE

              /RANDOM USE_INTERCEPT=TRUE SUBJECTS=ID_Egos_nivel2 COVARIANCE_TYPE=VARIANCE_COMPONENTS SOLUTION=FALSE

              /BUILD_OPTIONS TARGET_CATEGORY_ORDER=ASCENDING INPUTS_CATEGORY_ORDER=ASCENDING MAX_ITERATIONS=400

                CONFIDENCE_LEVEL=95 DF_METHOD=RESIDUAL COVB=MODEL PCONVERGE=0.000001(ABSOLUTE) SCORING=0

                SINGULAR=0.000000000001

              /EMMEANS_OPTIONS SCALE=ORIGINAL PADJUST=LSD.

     OUTPUT EXPORT

                      /CONTENTS  EXPORT=VISIBLE  LAYERS=VISIBLE  MODELVIEWS=VISIBLE

                      /XLSX  DOCUMENTFILE='C:\Users\34656\Desktop\GLMM.xlsx'

                 OPERATION=CREATESHEET

                 LOCATION=LASTROW      NOTESCAPTIONS=YES.

!DOEND

!ENDDEFINE.

macdef

                    BUSQUEDA_INFORMAL

                    BUSQUEDA_ONLINE

                    BUSQUEDA_FORMAL                    

                    INSERCION_INFORMAL

                    INSERCION_ONLINE

                    INSERCION_FORMAL  /.

 

 

The genlinmixed curiosity:

 

If the SUBJECTS keyworkd for the RANDOM subcommand is optional according to IBM (though IBM here does use it), what does it do to not specify SUBJECTS in RANDOM subcommand? I have been somehow answered to this already, something about the SPSS config and the longitudinal analysis, but I can’t figure out what it does (the results vary significantly if I don’t specify it). And I have also seen in some places that if you don’t specify that keywork, that is one of the reasons for the “non-positive definite Hesssian matrix” error to appear. This is only a curiosity, I have figured out already that I have to specify it, I would just like to understand it.

 

 

All the best,

Alejandro

===================== 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: LOOP + GENLINMIXED + SAVE OUTPUT in different excel sheets

Alejandro González Heras

You are a genious!!

 

It Works, but I’ll share the final code (thanks to you) so anyone could use it if interested:

 

*------------------------------------------------------------------

 

DEFINE macdef (!POS !CHAREND('/'))

!DO !i !IN (!1)

    FREQUENCIES !i.

       !LET !sheet = !QUOTE(!i) /* define macro variable */

                OUTPUT EXPORT

                  /CONTENTS  EXPORT=VISIBLE  LAYERS=VISIBLE  MODELVIEWS=VISIBLE

                  /XLSX  DOCUMENTFILE='C:\Users\34656\Desktop\GLMM.xlsx'

                 OPERATION=CREATESHEET

                 SHEET = !sheet /* use macro variable*/

                 LOCATION=LASTROW      NOTESCAPTIONS=YES.

    OUTPUT CLOSE *.

!DOEND

!ENDDEFINE.

macdef

Var1

Var2

Var3 /.

 

*---------------------------------------------------------------------

 

“OUTPUT CLOSE” command closes the output viewer at the end of every iteration and after saving it in an independent excel sheet. Otherwise it gets cumulative

 

Much appreciated,

Alejandro

 

 

 

De: [hidden email] <[hidden email]>
Enviado el: martes, 2 de marzo de 2021 13:03
Para: [hidden email]; Alejandro González Heras <[hidden email]>
Asunto: Re: LOOP + GENLINMIXED + SAVE OUTPUT in different excel sheets

 

Hi, Alejandro,

 

Q1: try sth. like this:

 

* -----------------------------------------------------------------------------------------------.

DEFINE macdef (!POS !CHAREND('/'))

...

!DO !i !IN (!1)

  !LET !sheet = !QUOTE(!i) /* define macro variable */

OUTPUT EXPORT

  /CONTENTS  EXPORT=VISIBLE  LAYERS=VISIBLE  MODELVIEWS=VISIBLE

  /XLSX  DOCUMENTFILE='C:\Users\34656\Desktop\GLMM.xlsx'

 OPERATION=CREATESHEET

 SHEET = !sheet /* use macro variable*/

 LOCATION=LASTROW      NOTESCAPTIONS=YES.

!DOEND

!ENDDEFINE.

* -----------------------------------------------------------------------------------------------.

 

GL,

Mario Giesel

Munich, Germany

 

Am Dienstag, 2. März 2021, 12:39:20 MEZ hat Alejandro González Heras <[hidden email]> Folgendes geschrieben:

 

 

Hi there,

 

I have two questions, one regarding the LOOP and one curiosity (at the end) about GENLINMIXED

 

So I have many target variables in a GENLINMIXED model I am running. First I created the loop, which works fine, and then I tried to save each analysis in one different excel sheet

 

The output export (see below) is supposed to save 6 times in the excel workbook in 6 different excel sheets. I skipped the excel sheet name because it would be overwritten. But I have to be missing something, since the code below saves the output 6 times, and as a result I have all the info (the 6 models) but in the same excel sheet (because the output is cumulative, if I were to write in syntax to close the output viewer, it only saves the last iteration).

 

DEFINE macdef (!POS !CHAREND('/'))

!DO !i !IN (!1)

    GENLINMIXED

              /DATA_STRUCTURE SUBJECTS=ID_Egos_nivel2

              /FIELDS TARGET=!i TRIALS=NONE OFFSET=NONE

              /TARGET_OPTIONS REFERENCE = 1 DISTRIBUTION=BINOMIAL LINK=LOGIT

              /FIXED  EFFECTS=

                  EDAD_EVENTO

                  cat.socioeco.origen.EGO

                  estudis_PARE_agrupat

                  estudis_ego_agrupat

                  Sexo_varon

                USE_INTERCEPT=TRUE

              /RANDOM USE_INTERCEPT=TRUE SUBJECTS=ID_Egos_nivel2 COVARIANCE_TYPE=VARIANCE_COMPONENTS SOLUTION=FALSE

              /BUILD_OPTIONS TARGET_CATEGORY_ORDER=ASCENDING INPUTS_CATEGORY_ORDER=ASCENDING MAX_ITERATIONS=400

                CONFIDENCE_LEVEL=95 DF_METHOD=RESIDUAL COVB=MODEL PCONVERGE=0.000001(ABSOLUTE) SCORING=0

                SINGULAR=0.000000000001

              /EMMEANS_OPTIONS SCALE=ORIGINAL PADJUST=LSD.

     OUTPUT EXPORT

                      /CONTENTS  EXPORT=VISIBLE  LAYERS=VISIBLE  MODELVIEWS=VISIBLE

                      /XLSX  DOCUMENTFILE='C:\Users\34656\Desktop\GLMM.xlsx'

                 OPERATION=CREATESHEET

                 LOCATION=LASTROW      NOTESCAPTIONS=YES.

!DOEND

!ENDDEFINE.

macdef

                    BUSQUEDA_INFORMAL

                    BUSQUEDA_ONLINE

                    BUSQUEDA_FORMAL                    

                    INSERCION_INFORMAL

                    INSERCION_ONLINE

                    INSERCION_FORMAL  /.

 

 

The genlinmixed curiosity:

 

If the SUBJECTS keyworkd for the RANDOM subcommand is optional according to IBM (though IBM here does use it), what does it do to not specify SUBJECTS in RANDOM subcommand? I have been somehow answered to this already, something about the SPSS config and the longitudinal analysis, but I can’t figure out what it does (the results vary significantly if I don’t specify it). And I have also seen in some places that if you don’t specify that keywork, that is one of the reasons for the “non-positive definite Hesssian matrix” error to appear. This is only a curiosity, I have figured out already that I have to specify it, I would just like to understand it.

 

 

All the best,

Alejandro

===================== 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: LOOP + GENLINMIXED + SAVE OUTPUT in different excel sheets

Bruce Weaver
Administrator
Alejandro, thank you for posting the final working solution.  That will make
it much easier for folks searching the archives in future.  

And well done, Mario.  You may want to print off Alejandro's reply, frame
it, and put it up on your office wall.  (Whenever we all get back to our
offices, that is!)  

;-)  



Alejandro González Heras wrote
> You are a genious!!
>
> It Works, but I’ll share the final code (thanks to you) so anyone could
> use it if interested:
>
> --- snip ---





-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
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
--
Bruce Weaver
bweaver@lakeheadu.ca
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.
Reply | Threaded
Open this post in threaded view
|

Re: LOOP + GENLINMIXED + SAVE OUTPUT in different excel sheets

spss.giesel@yahoo.de
:-))
Great idea, Bruce!
:-))

Am Dienstag, 2. März 2021, 14:56:58 MEZ hat Bruce Weaver <[hidden email]> Folgendes geschrieben:


Alejandro, thank you for posting the final working solution.  That will make
it much easier for folks searching the archives in future. 

And well done, Mario.  You may want to print off Alejandro's reply, frame
it, and put it up on your office wall.  (Whenever we all get back to our
offices, that is!) 

;-) 



Alejandro González Heras wrote
> You are a genious!!
>
> It Works, but I’ll share the final code (thanks to you) so anyone could
> use it if interested:
>
> --- snip ---





-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
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
===================== 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: LOOP + GENLINMIXED + SAVE OUTPUT in different excel sheets

Jon Peck
In reply to this post by Bruce Weaver
I would like to point out that there is a more general solution to this problem that can be used outside of macros.  OUTPUT EXPORT syntax cannot selectively export tables - you only have the "all" or "all visible choices.  The SPSSINC MODIFY OUTPUT extension command, which can be installed from the Extensions > Extension Hub menu, can export tables of specified type to Excel and put each table in a separate sheet.

Here is an example that creates sheets named table1, table2, etc with each containing a table of OMS type CustomTable.

SPSSINC MODIFY OUTPUT TABLES 
/IF SUBTYPE="'Custom Table'" PROCESS=ALL
/CUSTOM FUNCTION="customoutputfunctions.excelexport(file='c:/temp/extest.xls',
#  sheet='table#',action='CreateWorksheet')".

On Tue, Mar 2, 2021 at 6:56 AM Bruce Weaver <[hidden email]> wrote:
Alejandro, thank you for posting the final working solution.  That will make
it much easier for folks searching the archives in future. 

And well done, Mario.  You may want to print off Alejandro's reply, frame
it, and put it up on your office wall.  (Whenever we all get back to our
offices, that is!) 

;-) 



Alejandro González Heras wrote
> You are a genious!!
>
> It Works, but I’ll share the final code (thanks to you) so anyone could
> use it if interested:
>
> --- snip ---





-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

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

Re: LOOP + GENLINMIXED + SAVE OUTPUT in different excel sheets

Alejandro González Heras

Thank you all for your support

 

Truth is, Bruce, that Mario’s answer came as great help in a time of difficulty. You may notice that I am not used to programming so you may figure out the headache it gives you when something is not working and google knows nothing about it.

 

[hidden email], the SPSSINC MODIFY OUTPUT command is for custom tables only? Could I be able to select which tables of the output would be exported? For example, the random effects and fixed effect coefficients in GENLINMIXED output. Would that be possible?

 

Do notice that I had already a macro in order to run the regression model many times for a list of variables. And I wanted to export each model to a different excel sheet. The last example I gave, the final code, had a FREQUENCY command just to make it easier to read (GENLINMIXED has many more rows).

 

All the best,

A

 

De: SPSSX(r) Discussion <[hidden email]> En nombre de Jon Peck
Enviado el: martes, 2 de marzo de 2021 15:33
Para: [hidden email]
Asunto: Re: LOOP + GENLINMIXED + SAVE OUTPUT in different excel sheets

 

I would like to point out that there is a more general solution to this problem that can be used outside of macros.  OUTPUT EXPORT syntax cannot selectively export tables - you only have the "all" or "all visible choices.  The SPSSINC MODIFY OUTPUT extension command, which can be installed from the Extensions > Extension Hub menu, can export tables of specified type to Excel and put each table in a separate sheet.

 

Here is an example that creates sheets named table1, table2, etc with each containing a table of OMS type CustomTable.

 

SPSSINC MODIFY OUTPUT TABLES 

/IF SUBTYPE="'Custom Table'" PROCESS=ALL

/CUSTOM FUNCTION="customoutputfunctions.excelexport(file='c:/temp/extest.xls',

#  sheet='table#',action='CreateWorksheet')".

 

On Tue, Mar 2, 2021 at 6:56 AM Bruce Weaver <[hidden email]> wrote:

Alejandro, thank you for posting the final working solution.  That will make
it much easier for folks searching the archives in future. 

And well done, Mario.  You may want to print off Alejandro's reply, frame
it, and put it up on your office wall.  (Whenever we all get back to our
offices, that is!) 

;-) 



Alejandro González Heras wrote
> You are a genious!!
>
> It Works, but I’ll share the final code (thanks to you) so anyone could
> use it if interested:
>
> --- snip ---





-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

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

===================== 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: LOOP + GENLINMIXED + SAVE OUTPUT in different excel sheets

Jon Peck
You can use SPSSINC MODIFY OUTPUT (also on Utilities) with any table.  You just specify one or more OMS table types.  You can find the table type via Utilities OMS Control Panel or, easier, by right clicking in the output outline on an instance of a table and choosing Copy OMS subtype from the context menu.  

In the example, I specified PROCESS=ALL to process all the tables of the selected types in the Viewer, but you can specify PROCESS=PREVIOUS to only process the most recent command.

On Tue, Mar 2, 2021 at 8:27 AM Alejandro González Heras <[hidden email]> wrote:

Thank you all for your support

 

Truth is, Bruce, that Mario’s answer came as great help in a time of difficulty. You may notice that I am not used to programming so you may figure out the headache it gives you when something is not working and google knows nothing about it.

 

[hidden email], the SPSSINC MODIFY OUTPUT command is for custom tables only? Could I be able to select which tables of the output would be exported? For example, the random effects and fixed effect coefficients in GENLINMIXED output. Would that be possible?

 

Do notice that I had already a macro in order to run the regression model many times for a list of variables. And I wanted to export each model to a different excel sheet. The last example I gave, the final code, had a FREQUENCY command just to make it easier to read (GENLINMIXED has many more rows).

 

All the best,

A

 

De: SPSSX(r) Discussion <[hidden email]> En nombre de Jon Peck
Enviado el: martes, 2 de marzo de 2021 15:33
Para: [hidden email]
Asunto: Re: LOOP + GENLINMIXED + SAVE OUTPUT in different excel sheets

 

I would like to point out that there is a more general solution to this problem that can be used outside of macros.  OUTPUT EXPORT syntax cannot selectively export tables - you only have the "all" or "all visible choices.  The SPSSINC MODIFY OUTPUT extension command, which can be installed from the Extensions > Extension Hub menu, can export tables of specified type to Excel and put each table in a separate sheet.

 

Here is an example that creates sheets named table1, table2, etc with each containing a table of OMS type CustomTable.

 

SPSSINC MODIFY OUTPUT TABLES 

/IF SUBTYPE="'Custom Table'" PROCESS=ALL

/CUSTOM FUNCTION="customoutputfunctions.excelexport(file='c:/temp/extest.xls',

#  sheet='table#',action='CreateWorksheet')".

 

On Tue, Mar 2, 2021 at 6:56 AM Bruce Weaver <[hidden email]> wrote:

Alejandro, thank you for posting the final working solution.  That will make
it much easier for folks searching the archives in future. 

And well done, Mario.  You may want to print off Alejandro's reply, frame
it, and put it up on your office wall.  (Whenever we all get back to our
offices, that is!) 

;-) 



Alejandro González Heras wrote
> You are a genious!!
>
> It Works, but I’ll share the final code (thanks to you) so anyone could
> use it if interested:
>
> --- snip ---





-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

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

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