Automated Data Restructure

**Programming by Raynald Levesque applied to course rating data **.
**Visit SPSS Pages http://pages.infinit.net/rlevesqu/index.htm **.
**This example maintains a grouping factor while restructuring data from tall to wide **.
**Descriptive statistics are then generated off the wide data where each row represents an entity of study **.
**Approach allows generation of batches of reports for grouping factor **.
**Approach may be useful for creation of large historical data sets since each group reduces to one row of data **.

PRESERVE.
SET Printback=ON Length=None Width=80.

DATA LIST LIST /var1(A1) vec b a (3F8.0).
BEGIN DATA
A    1 2 3 
A    7 2 4
A    32 3 3
A    5 2 9
B    9 1 5
C    34 3 9
C    9 2 2
D    3 3 3 
D    7 8 8
D    0 2 0
D    1 1 1
END DATA.
SAVE OUTFILE='c:\\temp\\mydata.sav'.

GET FILE='c:\\temp\\mydata.sav'.

** Define a macro which will contain the number of required columns **.

SET MPRINT=no.

AGGREGATE OUTFILE=*
  /BREAK=var1
  /n = N(var1).

COMPUTE nobreak=1.
AGGREGATE OUTFILE=*
  /BREAK=nobreak
  /n = MAX(n).

DO IF $CASENUM=1.
WRITE OUTFILE='c:\\temp\\define n.sps' /'DEFINE !n()'n'!ENDDEFINE.'.
END IF.
EXECUTE.

INCLUDE FILE='c:\\temp\\define n.sps'.

** Now define a macro to restructure data from tall to wide **.

*////////////.
DEFINE !flip(!POS=!TOKENS(1) /!POS=!TOKENS(1) /vnames=!CMDEND)

GET FILE='c:\\temp\\mydata.sav'.
AUTORECODE VARIABLES=var1 /INTO var1num.

!LET !nbvar=!NULL
!DO !var !IN (!vnames)

  /* Count number of variables */
  !LET !nbvar=!CONCAT(!nbvar,"1")

  /* assign vnames to a vector */
- STRING !CONCAT('vname',!LENGTH(!nbvar)) (A8).
- COMPUTE !CONCAT('vname',!LENGTH(!nbvar))=!QUOTE(!var).

  /* Find last vname */
  !LET !lastnam=!var
!DOEND
!LET !nbvar=!LENGTH(!nbvar)

VECTOR vname=vname1 TO !CONCAT('vname',!nbvar)		
	/v=!HEAD(!vnames) TO !lastnam.

STRING vname(A8).
LOOP cnt=1 TO !nbvar.
- COMPUTE c=v(cnt).
- COMPUTE vname=vname(cnt).
- COMPUTE vnameidx=cnt.
- DO IF NOT SYSMIS(c).
-   XSAVE OUTFILE='c:\\temp\\temp.sav' /KEEP=var1 vname vnameidx c var1num.
- END IF.
END LOOP.
EXECUTE.

GET FILE='c:\\temp\\temp.sav'.
SORT CASES BY vnameidx var1.
COMPUTE casen=$CASENUM.

RANK VARIABLES=casen(A) BY vnameidx !1 /RANK INTO idx .

VECTOR c(!n).
COMPUTE c(idx)=c.
AGGREGATE OUTFILE=*
	/PRESORTED
	/BREAK=vnameidx var1num
	/var1 vname c1 TO !CONCAT('c',!n)=FIRST(var1 vname c1 TO !CONCAT('c',!n)).
ADD FILES FILE=* /DROP=vnameidx !1.
EXECUTE.

!ENDDEFINE.
*////////////.

** Call macro to do it **.
SET MPRINT=yes.
!flip var1num var1 vnames=vec b a.
SET MPRINT=no.



*Renaming vars.
RENAME VARS (VNAME C1 C2 C3 C4 = ITEM V1 V2 V3 V4).
EXECUTE.

**Now define a macro to calculate descriptive statistics **.

*////////////////////.
DEFINE !doit(nb=!TOKENS(1))

!LET !vlast=!CONCAT(v,!nb)
VECTOR v=v1 TO !vlast.
DO REPEAT cnt=1 TO 5 /c=c1 TO c5 /Nc=Nc1 TO Nc5.
. COUNT c=v1 TO !vlast (cnt).
. COUNT Nc=v1 TO !vlast (cnt).
. COMPUTE mean=MEAN(v1 TO !vlast).
. COMPUTE SD=SD(v1 TO !vlast).
. COMPUTE n=NVALID(v1 TO !vlast).
. COMPUTE c=c/n*100.
. COMPUTE NMis=NMIS(v1 TO !vlast).
. FORMATS c(PCT4.0) NMis Nc(COMMA8).
END REPEAT PRINT.

*STRING Item(A4).
*COMPUTE Item = SUBSTR(case_lbl,2,2).
FORMATS n(COMMA8) mean (F8.2) sd (F8.2).
VARIABLE LABEL mean "Mean"  
	Nc1 "S-D" Nc2 "D" Nc3 "Neut" Nc4 "A" Nc5 "S-A"
	c1 "S-D%" c2 "D%" c3 "Neut%" c4 "A%" c5 "S-A%" NMis "N_Mis".
!ENDDEFINE.
*////   /////  //////.


* Call macro to do it.
SET MPRINT=yes.
!doit nb=!n.
SET MPRINT=no.

SORT CASES BY VAR1 ITEM.
EXECUTE.
SPLIT FILE SEPARATE BY var1.
Report
  /FORMAT= CHWRAP(ON) PREVIEW(OFF) CHALIGN(BOTTOM)  UNDERSCORE(ON)
  ONEBREAKCOLUMN(OFF) CHDSPACE(1)  SUMSPACE(0)  AUTOMATIC  LIST
   BRKSPACE(-1)
  PAGE(1) MISSING'.' LENGTH(1, 71) ALIGN(LEFT) TSPACE(1) FTSPACE(1)
  MARGINS(1,101)
 /TITLE=
  CENTER 'Test Report' 
	')Year (Year could be passed via a Script)' 
	'Item Statistics*'
  LEFT ''
 /FOOTNOTE=
  LEFT '* Mean based on scale where 5=Strongly Agree; 4=Agree; 3=Uncertain; 2=Disagree; 1=Strongly Disagree;'
           '  SD=Standard Deviation (SD measures rating variability); N=Number of valid student ratings;'
           '  N_Mis=Number of Does-Not-Apply ratings plus missing values due to omits/unscannable markings;'
           '  Statistics reported based on valid N only; N_Mis values are excluded from statistics reported;'
           '  Raw counts for response-option percents can be calculated from % involved times valid N.'
   /VARIABLES
 mean  (VALUES)  (RIGHT)  (OFFSET(0)) (4)
 sd  (VALUES)  (RIGHT)  (OFFSET(0)) (4)
 n  (VALUES)  (RIGHT)  (OFFSET(0)) (4)
 nmis  (VALUES)  (RIGHT)  (OFFSET(0)) (5)
 SEP1(DUMMY) (3) ' '
 c5  (VALUES)  (RIGHT)  (OFFSET(0)) (5)
 c4  (VALUES)  (RIGHT)  (OFFSET(0)) (5)
 c3  (VALUES)  (RIGHT)  (OFFSET(0)) (5)
 c2  (VALUES)  (RIGHT)  (OFFSET(0)) (5)
 c1  (VALUES)  (RIGHT)  (OFFSET(0)) (5)
 /BREAK   item  (LABELS)  (LEFT)  (OFFSET(0)) (10) .

SPLIT FILE OFF.


**Optional additions: Scripts referred to below can save work and speed process of communicating output **.

**First, since this example may be useful for adding groups or batches of data to a larger file, a time identifier may be helpful **.
**You could begin program with a script that asks the user to specify time related data such as month and/or year **.
**This time specific data then becomes part of the dataset and can be passed to the Report Title via ')var' where var is the name of the variable passed **.
**See Raynald Levesque script at: http://pages.infinit.net/rlevesqu/Scripts/dialogs/GetFyFromUserThenRunSyntax.txt **.
**Near the start of your program you would insert a statement similar to the following where you specify the path to the script **.
**SCRIPT FILE='C:\\--(substitute your path here and use sbs name extension)--\\GetFyFromUserThenRunSyntax.sbs'. **.

**Second: You could run the SPSS Clean Viewer script that deletes unwanted output **.
**See http://pages.infinit.net/rlevesqu/Scripts/OutputDoc/DeleteStatisticsAndCaseProcessingSummary.txt **. 
**SCRIPT FILE='C:\\--(substitute your path here)--\\Clean Viewer.sbs'. **.

**Third: You could run a script that exports your output to MS Word for printing **.
**See Raynald Levesque script at: http://pages.infinit.net/rlevesqu/Scripts/ImportExport/ExportVisibleOutputToWordViaHTML.txt **.

*Fourth: If all output were going to the same person, you could run a script that emails output to programmed address.
**See Raynald Levesque script at: http://pages.infinit.net/rlevesqu/Scripts/ImportExport/SendDocumentByEmail.txt **.

*Next command restores settings preserved at start.
RESTORE.