LOOP + GENLINMIXED + SAVE OUTPUT in different excel sheets
Locked
LOOP + GENLINMIXED + SAVE OUTPUT in different excel sheets
 
|
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 |
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 |
Re: LOOP + GENLINMIXED + SAVE OUTPUT in different excel sheets
|
|
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]> 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 |
Re: LOOP + GENLINMIXED + SAVE OUTPUT in different excel sheets
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." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
Re: LOOP + GENLINMIXED + SAVE OUTPUT in different excel sheets
|
|
:-)) 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 |
|
|
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 |
Re: LOOP + GENLINMIXED + SAVE OUTPUT in different excel sheets
|
|
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 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:
-- Jon K Peck ===================== 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
|
|
|
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:
|
| Free forum by Nabble | Edit this page |