Looping analysis based on demographic variable

[Vanessa K]

HI there,

I have a number of tables I want to produce based on different sites. There are roughly 1300 sites and I want to export each set of tables to a new worksheet in excel so that each site gets its own sheet. Is there a simple way of doing this using some sort of looping function? Currently I have created thousands of rows of syntax and used the select cases function but my process just dies after about the 28th site. Here is an example of what I am doing. Hope someone can help.

Cheers Vanessa.

DATASET ACTIVATE GFoolLevelAll.

USE ALL.
COMPUTE filter_$=(SiteID = 10001).
VARIABLE LABELS filter_$ 'SiteID = 2 (FILTER)'.
VALUE LABELS filter_$ 0 'Not Selected' 1 'Selected'.
FORMATS filter_$ (f1.0).
FILTER BY filter_$.
EXECUTE.

* Custom Tables.
CTABLES
  /VLABELS VARIABLES=SiteName SiteID N_BREAK DISPLAY=DEFAULT
  /TABLE BY SiteName [COUNT F40.0] + SiteID [MEAN] + N_BREAK [COUNT F40.0]
  /CATEGORIES VARIABLES=SiteName ORDER=A KEY=VALUE EMPTY=EXCLUDE
  /CATEGORIES VARIABLES=N_BREAK ORDER=A KEY=VALUE EMPTY=EXCLUDE.



* Custom Tables.
CTABLES
  /VLABELS VARIABLES=GFIndec_GFale GFCE_GFale GFCR_GFale GFAE_GFale GFCFSL_GFale GFGVC_GFale
    GFTSP_GFale GFTRC_GFale GFTEC_GFale GFTPI_GFale GFSB_GFale
    DISPLAY=DEFAULT
  /TABLE GFIndec_GFale [MEAN] + GFCE_GFale [MEAN] + GFCR_GFale [MEAN] + GFAE_GFale [MEAN] +
    GFCFSL_GFale [MEAN] + GFGVC_GFale [MEAN] + GFTSP_GFale [MEAN] + GFTRC_GFale [MEAN] +
    GFTEC_GFale [MEAN] + GFTPI_GFale [MEAN] + GFSB_GFale [MEAN]
/TITLES  
CORNER='Site  summary chart data whole Site  '.


DATASET ACTIVATE GFJ_Site_level_PT_only.
USE ALL.
COMPUTE filter_$=(SiteID = 2).
VARIABLE LABELS filter_$ 'SiteID = 2 (FILTER)'.
VALUE LABELS filter_$ 0 'Not Selected' 1 'Selected'.
FORMATS filter_$ (f1.0).
FILTER BY filter_$.
EXECUTE.

*PTonly

* Custom Tables.
CTABLES
  /VLABELS VARIABLES=GFIndec_GFale GFCE_GFale GFCR_GFale GFAE_GFale GFCFSL_GFale GFGVC_GFale
    GFTSP_GFale GFTRC_GFale GFTEC_GFale GFTPI_GFale GFSB_GFale
    DISPLAY=DEFAULT
  /TABLE GFIndec_GFale [MEAN] + GFCE_GFale [MEAN] + GFCR_GFale [MEAN] + GFAE_GFale [MEAN] +
    GFCFSL_GFale [MEAN] + GFGVC_GFale [MEAN] + GFTSP_GFale [MEAN] + GFTRC_GFale [MEAN] +
    GFTEC_GFale [MEAN] + GFTPI_GFale [MEAN] + GFSB_GFale [MEAN]
/TITLES  
CORNER='Site  summary chart data PT only '.

* Export Output.
OUTPUT EXPORT
  /CONTENTS  EXPORT=VISIBLE  LAYERS=VISIBLE  MODELVIEWS=VISIBLE
  /XLS  DOCUMENTFILE='F:\CUST-GFJ\GFJ-SitesSurvey-2014-601\Analysis\GFJ all Site '+
    'report tables.xls'
     OPERATION=CREATESHEET  SHEET='SiteID'
     LOCATION=LASTCOLUMN  NOTEGFAPTIONS=NO.
OUTPUT CLOSE *.

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

[David Marso]

Look up DEFINE !ENDDEFINE and see !DO !DOEND and !CONCAT
HTH

Vanessa K wrote

HI there,

I have a number of tables I want to produce based on different sites. There are roughly 1300 sites and I want to export each set of tables to a new worksheet in excel so that each site gets its own sheet. Is there a simple way of doing this using some sort of looping function? Currently I have created thousands of rows of syntax and used the select cases function but my process just dies after about the 28th site. Here is an example of what I am doing. Hope someone can help.

Cheers Vanessa.

DATASET ACTIVATE GFoolLevelAll.

USE ALL.
COMPUTE filter_$=(SiteID = 10001).
VARIABLE LABELS filter_$ 'SiteID = 2 (FILTER)'.
VALUE LABELS filter_$ 0 'Not Selected' 1 'Selected'.
FORMATS filter_$ (f1.0).
FILTER BY filter_$.
EXECUTE.

* Custom Tables.
CTABLES
  /VLABELS VARIABLES=SiteName SiteID N_BREAK DISPLAY=DEFAULT
  /TABLE BY SiteName [COUNT F40.0] + SiteID [MEAN] + N_BREAK [COUNT F40.0]
  /CATEGORIES VARIABLES=SiteName ORDER=A KEY=VALUE EMPTY=EXCLUDE
  /CATEGORIES VARIABLES=N_BREAK ORDER=A KEY=VALUE EMPTY=EXCLUDE.



* Custom Tables.
CTABLES
  /VLABELS VARIABLES=GFIndec_GFale GFCE_GFale GFCR_GFale GFAE_GFale GFCFSL_GFale GFGVC_GFale
    GFTSP_GFale GFTRC_GFale GFTEC_GFale GFTPI_GFale GFSB_GFale
    DISPLAY=DEFAULT
  /TABLE GFIndec_GFale [MEAN] + GFCE_GFale [MEAN] + GFCR_GFale [MEAN] + GFAE_GFale [MEAN] +
    GFCFSL_GFale [MEAN] + GFGVC_GFale [MEAN] + GFTSP_GFale [MEAN] + GFTRC_GFale [MEAN] +
    GFTEC_GFale [MEAN] + GFTPI_GFale [MEAN] + GFSB_GFale [MEAN]
/TITLES  
CORNER='Site  summary chart data whole Site  '.


DATASET ACTIVATE GFJ_Site_level_PT_only.
USE ALL.
COMPUTE filter_$=(SiteID = 2).
VARIABLE LABELS filter_$ 'SiteID = 2 (FILTER)'.
VALUE LABELS filter_$ 0 'Not Selected' 1 'Selected'.
FORMATS filter_$ (f1.0).
FILTER BY filter_$.
EXECUTE.

*PTonly

* Custom Tables.
CTABLES
  /VLABELS VARIABLES=GFIndec_GFale GFCE_GFale GFCR_GFale GFAE_GFale GFCFSL_GFale GFGVC_GFale
    GFTSP_GFale GFTRC_GFale GFTEC_GFale GFTPI_GFale GFSB_GFale
    DISPLAY=DEFAULT
  /TABLE GFIndec_GFale [MEAN] + GFCE_GFale [MEAN] + GFCR_GFale [MEAN] + GFAE_GFale [MEAN] +
    GFCFSL_GFale [MEAN] + GFGVC_GFale [MEAN] + GFTSP_GFale [MEAN] + GFTRC_GFale [MEAN] +
    GFTEC_GFale [MEAN] + GFTPI_GFale [MEAN] + GFSB_GFale [MEAN]
/TITLES  
CORNER='Site  summary chart data PT only '.

* Export Output.
OUTPUT EXPORT
  /CONTENTS  EXPORT=VISIBLE  LAYERS=VISIBLE  MODELVIEWS=VISIBLE
  /XLS  DOCUMENTFILE='F:\CUST-GFJ\GFJ-SitesSurvey-2014-601\Analysis\GFJ all Site '+
    'report tables.xls'
     OPERATION=CREATESHEET  SHEET='SiteID'
     LOCATION=LASTCOLUMN  NOTEGFAPTIONS=NO.
OUTPUT CLOSE *.

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