Automated restructure from long to wide

 

Download as .sps Shows as plain text

 

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
run name        transform from long to wide  /spss/ltow.sp  .
*                                                                    .
*    this program will transform a file from many to one              .
*    all variable names and formats will be keept                    .
*    if variable name truncated to a7 are duplicates the programe will fix it .
*    the program was tested on spss6.1 for unix and spss9 for windows .
*    this is the windows version - treatment of files path is complicated .
*    check carefully when changing the path parameter                .
*    let me know if you have comments - hilel@bgumail.bg.ac.il            .
*                                                                   .
set             width=130 mxwars=2000 length=120 printback=listing  .
set             mprint=yes                                          .
define   ltow    ( outfile  !tokens (1)
                /  infile   !tokens (1)
                /  repeat   !tokens (1)
                /  path     !tokens (1)
                /  sortvar  !enclose ('(',')')
                /  dropvar  !enclose ('{','}')
                /  timestr  !enclose ('[',']')  )                 .
*  the file need a repeat var  .
*  the key (sort) var should be droped we don't want to rename them .
get             file= !infile  /drop = !dropvar                 .
n of cases      1                                               .
execute                                                         .
flip                                                            .
rename var      case_lbl = varname                              .
compute         varnum =$casenum                                .
list                                                            .
sort cases      by varname                                      .
!let            !filenm =!quote(!concat(!unquote(!path),'tmp.sv'))    .
aggregate       outfile=!filenm /break = varname / nvar=n(varname) .
execute                                                           .
match files     file=  * / table=!filenm / by varname / first=start.
string          newvar(a7)                                        .
numeric         seqnum(f1)                                        .
leave           seqnum                                            .
if              start eq 1    seqnum=0                            .
compute         seqnum=sum(seqnum,1)                              .
compute         newvar=varname                                    .
do if           nvar gt 1                                         .
compute         substr(newvar,7,1)=string(seqnum,f1)              .
end if                                                            .
sort cases     by varnum                                          .
!let            !filenm =!quote(!concat(!unquote(!path),'dic.sv'))    .
save            outfile=!filenm / drop var001 / map            .
execute                                                           .
list                                                              .
get             file= !filenm / map                              .
string          newname(a8) groupstr(a1)                          .
!do !aa  !in ( !timestr  )                                        .
compute        groupstr=!quote(!aa)                               .
compute         newname = concat(rtrim(newvar,' '),!quote(!aa))   .
!let            !filenm =!quote(!concat(!unquote(!path),'dic.new'))   .
print           outfile=!filenm  /
                varnum, groupstr,  newvar  , newname  (f4  a1 a7  a8).
!doend                                                               .
execute                                                              .

data list       file=!filenm  records=1 /
                varnum, groupstr, newvar , newname(1x f4  a1 a7  a8) .
compute       dummi = 1                                              .
sort cases    by dummi                                               .
add files     file=* / by dummi    / last=lastone                    .
sort cases    by groupstr varnum                                     .
numeric         groupnum(f2)                                         .
leave           groupnum                                             .
if              varnum eq 1   groupnum=sum(groupnum,1)               .
add files     file=* / by groupstr / last=fin                        .
list                                                                 .
!let            !filenm =!quote(!concat(!unquote(!path),'dic.sps'))   .
do if          varnum eq 1                                           .
print           outfile=!filenm /
          "get          file=  ",!quote(!infile)  /
          "select if     " , !quote(!repeat) , " = " , groupnum /
          "rename var    "                                           .
end if                                                               .
print           outfile=!filenm  /
                "         (" ,  newvar  , " = " newname  ,")"        .
do if          fin eq 1                                              .
string          filename(a40)                                   .
compute         filename =concat(!path,
                !quote(!outfile),groupstr,'.sav'  )  .
print           outfile=!filenm /
          "xsave      outfile=  " , "'", filename ,"'"     /
          "execute       "                                            .
end if                                                                .
compute         case1=$casenum                                        .
do if           lastone eq 1                                          .
print           outfile=!filenm  / "match file"                       .
!do !aa  !in ( !timestr  )                                            .
compute          filename =!quote(!concat(!unquote(!path),!unquote(!outfile),
                     !unquote(!aa),'.sav'))                           .
print           outfile=!filenm  /
                "           file = " , "'", filename  , "'" , " /  "  .
!doend                                                                .
compute         filename =!quote(!concat(!unquote(!path),!unquote(!outfile),
                '.sav' ))                                             .
compute         filename=rtrim(filename)                              .
print           outfile=!filenm
                / "        by   " ,!quote(!sortvar)  , "/ map " / "execute "/
                  "save         outfile= " ,"'", filename ,"'" /
                   "execute  "                                        .
end if                                                                .
execute                                                               .
data list       file=!filenm  records=1 / line ( 1x a100)             .
execute                                                               .
list                                                                  .
write            outfile=!filenm /        line (a100)                 .
execute                                                               .
set             mprint=off                                          .
include            !filenm                                            .
!enddefine                                                            .

*   help for parmetres                                                .
*             sortvar  the variables that sort the infile - 1. case number .
*                      2. the repeat variable                         .
*             repeat   a sequencial variable within the case          .
*             infile   the original name of the data file                         .
*             outfile  the name of the newfile that the program create.
*             dropvar  key variable not to by duplicated               .
*             timestr  the suffix for each repeat                      .

* Calling the macro.

ltow               outfile = newfile
                   repeat  = repeat
                   sortvar = ( sno )
                   dropvar = { sno }
                   timestr = [ a b ]
                   infile  = 'c:/temp/sleep-dis2.sav'
                   path    = 'c:/temp/'                .
Related pages

...

Navigate from here