Looping

[Jim Van Overschelde]

I want to be able to execute a macro but feed the macro a variable that
relates to the names of the files it is loading and processing.
I have yet to find a good way to do this in SPSS syntax.  Any suggestions
for replacing the 13-line section that repeatedly calls the macro?

Thanks,
Jim

*******************************************************
new file.
dataset name blankset.

define !XLS2SAV (!pos !tokens(1) /!pos !tokens(1)).
GET DATA /TYPE=XLSX
  /file=!quote(!concat('D:\SBEC\tests\',!1,'-',!2,'.xlsx'))
  /sheet= index 1
  /CELLRANGE=full
  /READNAMES=on
  /ASSUMEDSTRWIDTH=32767.
EXECUTE.
Dataset name XLSX.

alter type firstname(a20) middlename(a20) lastname(a20) ethnicity(f12.0)
testname(a48)
birthdate(DATE11) overallscaledscore(a3) scaledscore1(a3) scaledscore2(a3)
scaledscore3(a3) scaledscore4(a3)
scaledscore5(a3) result(a1) ethnicitydesc(a50) routedesc(a75).
save outfile = !quote(!concat('D:\SBEC\tests\',!1,'-',!2,'.sav')).
dataset activate blankset.
dataset close xlsx.
!enddefine.

!XLS2SAV 01012000 12312000.
!XLS2SAV 01012001 12312001.
!XLS2SAV 01012002 12312002.
!XLS2SAV 01012003 12312003.
!XLS2SAV 01012004 12312004.
!XLS2SAV 01012005 12312005.
!XLS2SAV 01012006 12312006.
!XLS2SAV 01012007 12312007.
!XLS2SAV 01012008 12312008.
!XLS2SAV 01012009 12312009.
!XLS2SAV 01012010 12312010.
!XLS2SAV 01012011 12312011.
!XLS2SAV 01012012 11012012.

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

I don't have SPSS on this machine, so this is untested, but I think it should work.  The !HEAD - !TAIL trick is one I learned from David Marso.


DEFINE !XLS2SAV (List1 !CHAREND('/') / List2 !CMEND)

!LET !COPY2=!LIST2
!DO !A !IN (!LIST1) !LET !B = !HEAD(!COPY2) !LET !COPY2=!TAIL(!COPY2)

GET DATA /TYPE=XLSX
  /file=!quote(!concat('D:\SBEC\tests\',!A,'-',!B,'.xlsx'))
  /sheet= index 1
  /CELLRANGE=full
  /READNAMES=on
  /ASSUMEDSTRWIDTH=32767.
EXECUTE.
Dataset name XLSX.

alter type firstname(a20) middlename(a20) lastname(a20) ethnicity(f12.0)
testname(a48)
birthdate(DATE11) overallscaledscore(a3) scaledscore1(a3) scaledscore2(a3)
scaledscore3(a3) scaledscore4(a3)
scaledscore5(a3) result(a1) ethnicitydesc(a50) routedesc(a75).
save outfile = !quote(!concat('D:\SBEC\tests\',!A,'-',!B,'.sav')).
dataset activate blankset.
dataset close xlsx.
!ENDDEFINE.

* Call the macro with two lists of arguments.

!XLS2SAV
 A = 01012000 01012001 01012002 01012003 /
 B = 12312000 12312001 12312002 12312003.

Jim Van Overschelde wrote

I want to be able to execute a macro but feed the macro a variable that
relates to the names of the files it is loading and processing.
I have yet to find a good way to do this in SPSS syntax.  Any suggestions
for replacing the 13-line section that repeatedly calls the macro?

Thanks,
Jim

*******************************************************
new file.
dataset name blankset.

define !XLS2SAV (!pos !tokens(1) /!pos !tokens(1)).
GET DATA /TYPE=XLSX
  /file=!quote(!concat('D:\SBEC\tests\',!1,'-',!2,'.xlsx'))
  /sheet= index 1
  /CELLRANGE=full
  /READNAMES=on
  /ASSUMEDSTRWIDTH=32767.
EXECUTE.
Dataset name XLSX.

alter type firstname(a20) middlename(a20) lastname(a20) ethnicity(f12.0)
testname(a48)
birthdate(DATE11) overallscaledscore(a3) scaledscore1(a3) scaledscore2(a3)
scaledscore3(a3) scaledscore4(a3)
scaledscore5(a3) result(a1) ethnicitydesc(a50) routedesc(a75).
save outfile = !quote(!concat('D:\SBEC\tests\',!1,'-',!2,'.sav')).
dataset activate blankset.
dataset close xlsx.
!enddefine.

!XLS2SAV 01012000 12312000.
!XLS2SAV 01012001 12312001.
!XLS2SAV 01012002 12312002.
!XLS2SAV 01012003 12312003.
!XLS2SAV 01012004 12312004.
!XLS2SAV 01012005 12312005.
!XLS2SAV 01012006 12312006.
!XLS2SAV 01012007 12312007.
!XLS2SAV 01012008 12312008.
!XLS2SAV 01012009 12312009.
!XLS2SAV 01012010 12312010.
!XLS2SAV 01012011 12312011.
!XLS2SAV 01012012 11012012.

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

[Andy W]

Assuming that the file naming structure will always be `0101YEAR-1231YEAR`, I would simplify Bruce's approach even more, and just have a loop within the macro specifying the begin and end years. Example below (untested as I don't have any xlsx files to test it on and I'm too lazy to make one that fits the bill).

**********************************************.
define !XLS2SAV (!pos !tokens(1) /!pos !tokens(1)).

!DO !i = !1 !TO !2

GET DATA /TYPE=XLSX
  /file=!quote(!concat('D:\SBEC\tests\0101',!i,'-1231',!i,'.xlsx'))
  /sheet= index 1
  /CELLRANGE=full
  /READNAMES=on
  /ASSUMEDSTRWIDTH=32767.
EXECUTE.
Dataset name XLSX.

alter type firstname(a20) middlename(a20) lastname(a20) ethnicity(f12.0)
testname(a48)
birthdate(DATE11) overallscaledscore(a3) scaledscore1(a3) scaledscore2(a3)
scaledscore3(a3) scaledscore4(a3)
scaledscore5(a3) result(a1) ethnicitydesc(a50) routedesc(a75).
save outfile = !quote(!concat('D:\SBEC\tests\0101',!i,'-1231',!i,'.sav')).
dataset activate blankset.
dataset close xlsx.
!DOEND
!enddefine.

!XLS2SAV 2000 2012.
**********************************************.

IMO the first solution Jim posted is only bad/annoying if you need to do it once - if you need to update this every year it is best the save the results and concatenate new years to it. That is no need to re-compile old years every year if they are static files.