I have an excel worksheet containing thousands of file paths. I have imported them into an spss dataset. The goal is to rename the all the files that live within those paths. My plan is to run a mass GET FILE
/ SAVE OUTFILE
loop to get it done. I already have code that 99% works.
The problem is that the file paths do not import from excel with quotations around them. So when I run my GET
/SAVE
macro there are no quotations around the file paths and the GET FILE
can not run. I can not figure out the best way to add quotes around the file paths.
Here is the macro code I am using for the loop. Probably not the most efficient, but it does what I need.
SET MPRINT=no.
DEFINE !GETPATH (FPATH=!TOKENS(1)
/VARG=!TOKENS(1)
/VARS=!TOKENS(1))
GET FILE=!FPATH.
COMPUTE nobreak=1.
AGGREGATE
/OUTFILE=*
/BREAK=!VARG !VARS
/nbval = N(nobreak).
COMPUTE nobreak=1.
MATCH FILES FILE=* /BY nobreak /LAST=last.
DO IF $CASENUM=1.
- WRITE OUTFILE='a file path here\getsave.sps' / 'GET ' 'FILE ' !VARG'.'.
- WRITE OUTFILE='a file path here\getsave.sps' / 'SAVE ' 'OUTFILE ' !VARS'.'.
ELSE IF NOT last.
- WRITE OUTFILE='a file path here\getsave.sps' / 'GET ' 'FILE ' !VARG'.'.
- WRITE OUTFILE='a file path here\getsave.sps' / 'SAVE ' 'OUTFILE ' !VARS'.'.
ELSE.
- WRITE OUTFILE='a file path here\getsave.sps' / 'GET ' 'FILE ' !VARG'.'.
- WRITE OUTFILE='a file path here\getsave.sps' / 'SAVE ' 'OUTFILE ' !VARS'.'.
ELSE.
END IF.
EXECUTE.
GET FILE=!FPATH.
INCLUDE 'a file path here\getsave.sps'.
!ENDDEFINE.
!GETPATH
FPATH ='filepath to dataset holding the filepaths\Data5.sav'
VARG =GetFile
VARS =SaveOutFile.
GetFile and SaveOutFile are the variable names holding the respective filepaths.
I have tried using the !QUOTE()
and !CONCAT()
commands in various ways such as:
/BREAK=!VARG !VARS
'GET ' 'FILE ' !QUOTE(!VARG) '.'.
!LET !VAR1 = !QUOTE(!VARG)
!QUOTE(!CONCAT(!UNQUOTE(GET ), !UNQUOTE(FILE ), !VARG))'.'.
However, I can't seem to find away to get it to work.
What I expect to see is a file with many iterations of
GET FILE "abc.sav".
SAVE OUTFILE "xyz.sav".
that I can run.
Thanks to anyone that can help!
You can add the quotes as part of the non-dynamic text in the write out
command:
WRITE OUTFILE='a file path here\getsave.sps' / 'GET FILE="' !VARG '".' .
BTW this will work the same without wrapping it in a macro.
Unless you have a few files that contain lists, and unless there is a reason to differentiate between the first/middle/last lines in the list, the whole process could be done like this:
GET FILE="path\your file with the list.sav".
AGGREGATE / OUTFILE=* / BREAK=OrigName TargenName / n=n.
WRITE OUTFILE='a file path here\getsave.sps' / 'GET FILE="' OrigName '".'
/ 'SAVE OUTFILE= "' TargenName '".' .
EXECUTE.
insert file='a file path here\getsave.sps'.