SPSS Macro to open CSV from variable

Question

I am new to SPSS macros and trying to open a CSV file from a specified path in a variable.

Note that I want to set all variable settings at the top of the file, so I can easily decide to import another file with a different name.

This is my code:

/* settings */
define !SYMB() "VIX". !enddefine.
define !CSVFILE() "E:\Downloads\$" + !SYMB + ".csv". !enddefine.

/* import csv file */
GET DATA
  /TYPE=TXT
  /FILE=!CSVFILE
  /DELCASE=LINE
  /DELIMITERS=","
  /ARRANGEMENT=DELIMITED
  /FIRSTCASE=1
  /IMPORTCASE=ALL
  /VARIABLES=
  V1 5X
  Date ADATE10
  !SYMB + 'O' F7.2
  !SYMB + 'H' F7.2
  !SYMB + 'L' F7.2
  !SYMB + 'C' F7.2.
CACHE.
EXECUTE.
DATASET NAME "DataSet" + !SYMB WINDOW=FRONT.

Unfortunately I am getting the following errors:

/* settings */ define !SYMB() "VIX". !enddefine. define !CSVFILE() "E:\Downloads\$" + !SYMB + ".csv". !enddefine.

Warning # 207 in column 13. Text: E:\Downloads\$ A '+' was found following a text string, indicating continuation, but the next non-blank character was not a quotation mark or an apostrophe.

/* import csv file */ GET DATA /TYPE=TXT /FILE=!CSVFILE

Error. Command name: GET DATA (2256) Invalid subcommand: FILE Execution of this command stops.

Error # 1. Command name: + The first word in the line is not recognized as an SPSS Statistics command. Execution of this command stops. /DELCASE=LINE

Error # 1. Command name: /DELCASE The first word in the line is not recognized as an SPSS Statistics command. Execution of this command stops. /DELIMITERS="," /ARRANGEMENT=DELIMITED /FIRSTCASE=1 /IMPORTCASE=ALL /VARIABLES= V1 5X Date ADATE10 !SYMB + 'O' F7.2

Error # 1. Command name: + The first word in the line is not recognized as an SPSS Statistics command. Execution of this command stops. !SYMB + 'H' F7.2

Error # 1. Command name: + The first word in the line is not recognized as an SPSS Statistics command. Execution of this command stops. !SYMB + 'L' F7.2

Error # 1. Command name: + The first word in the line is not recognized as an SPSS Statistics command. Execution of this command stops. !SYMB + 'C' F7.2.

Error # 1. Command name: + The first word in the line is not recognized as an SPSS Statistics command. Execution of this command stops. CACHE. EXECUTE.

Error # 105. Command name: EXECUTE This command is not valid before a working file has been defined. Execution of this command stops. DATASET NAME "DataSet" + !SYMB WINDOW=FRONT.

How should I be doing this?

Output on suggested macro:

/* settings */
define !SYMB() "VIX" !enddefine.
define !CSVFILE() !quo(!con("E:\Downloads\$", !unq(!eva(!SYMB)), ".csv")) !enddefine.
define !c(str1 = !tok(1) /str2 = !tok(1)) !con(!unq(str1), !unq(str2)) !end.
define !cq(str1 = !tok(1) /str2 = !tok(1)) !quo(!con(!unq(str1), !unq(str2))) !end.

/* import csv file */
GET DATA
  /TYPE=TXT
  /FILE=!CSVFILE /DELCASE=LINE
  /DELIMITERS=","
  /ARRANGEMENT=DELIMITED
  /FIRSTCASE=1
  /IMPORTCASE=ALL
  /VARIABLES=
  V1 5X
  Date ADATE10
  !c !str1=!SYMB str2="O" F7.2

>Warning # 210 in column 2.  Text: !str1
>A macro symbol is invalid in this context.
>The symbol will be treated as an invalid special character.
  !c !str1=!SYMB str2="H" F7.2

>Warning # 210 in column 2.  Text: !str1
>A macro symbol is invalid in this context.
>The symbol will be treated as an invalid special character.
  !c !str1=!SYMB str2="L" F7.2

>Warning # 210 in column 2.  Text: !str1
>A macro symbol is invalid in this context.
>The symbol will be treated as an invalid special character.
  !c !str1=!SYMB str2="C" F7.2.

>Warning # 210 in column 2.  Text: !str1
>A macro symbol is invalid in this context.
>The symbol will be treated as an invalid special character.

>Error.  Command name: GET DATA
>(2265) Unrecognized or invalid variable format.  The format is invalid.  For
>numeric formats, the width or decimals value may be invalid.
>Execution of this command stops.
CACHE.
EXECUTE.

>Error # 105.  Command name: EXECUTE
>This command is not valid before a working file has been defined.
>Execution of this command stops.
DATASET NAME !cq !str1="DataSet" !str2=!SYMB WINDOW=FRONT.

Test output:

set err=off.
set mpr=on.
set printback=on.
  11  0 M>  set printback=on.

  12  0 M>  
!c str1="A" str2="B".
  13  0 M>  !c str1="A" str2="B".
!cq str1="A" str2="B".
  14  0 M>  !cq str1="A" str2="B".

  15  0 M>  
set printback=off.
  16  0 M>  set printback=off.

You have to define !c() and !cq() before running the test.

djhurio
– djhurio

2012-01-13 20:55:03 +00:00
Commented Jan 13, 2012 at 20:55 — djhurio
– djhurio, Commented Jan 13, 2012 at 20:55

djhurio · Accepted Answer · 2012-01-09 21:42:26Z

2

SPSS macro language is tricky.

1) You have to do concatenation of strings inside macro;

2) Avoid dots in macro commands - if they are not necessary;

3) Do not end the line with macro command.

Try this untested syntax:

/* settings */
define !SYMB() "VIX" !enddefine.
define !CSVFILE() !quo(!con("E:\Downloads\$", !unq(!eva(!SYMB)), ".csv")) !enddefine.
define !c(str1 = !tok(1) /str2 = !tok(1)) !con(!unq(!str1), !unq(!str2)) !end.
define !cq(str1 = !tok(1) /str2 = !tok(1)) !quo(!con(!unq(!str1), !unq(!str2))) !end.

/* import csv file */
GET DATA
  /TYPE=TXT
  /FILE=!CSVFILE /DELCASE=LINE
  /DELIMITERS=","
  /ARRANGEMENT=DELIMITED
  /FIRSTCASE=1
  /IMPORTCASE=ALL
  /VARIABLES=
    V1 5X
    Date ADATE10
    !c str1=!SYMB str2="O" F7.2
    !c str1=!SYMB str2="H" F7.2
    !c str1=!SYMB str2="L" F7.2
    !c str1=!SYMB str2="C" F7.2.

EXECUTE.

DATASET NAME !cq str1="DataSet" str2=!SYMB WINDOW=FRONT.

Could you please run this test syntax and post the result you get in the Output.

set err=off.
set mpr=on.
set printback=on.

!c str1="A" str2="B".
!cq str1="A" str2="B".

set printback=off.
set mpr=off.
set err=on.

edited Jan 9, 2012 at 21:42

answered Jan 8, 2012 at 22:06

djhurio

5,5364 gold badges29 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Tom Over a year ago

Thanks, but this does not seem to run. What is !quo, !unq and !eva? When I run the code I get the output I have added to my original post.

djhurio Over a year ago

These are short names for macro functions !quote(), !unquote() and !eval(). I did some error correction. Try to run the code now. Unfortunately I do not have SPSS with me now. I can not test the code.

Tom Over a year ago

That almost works. What is the difference between !c and !cq? I am getting this error: Warnings Expecting dataset name or WINDOW. Found DataSetVIX. Execution of this command stops.

djhurio Over a year ago

!c() is for combining strings with unquoted result. !cq() is the same, but the result should be quoted. See my edited answer.

djhurio Over a year ago

Seems it works for me: !c str1=A str2=B. 88 0 M> AB !cq str1=A str2=B. 90 0 M> 'AB'

|

JKP · Accepted Answer · 2012-01-09 12:57:57Z

0

Macros are indeed tricky. For your purposes, I suggest that you use the simpler FILE HANDLE command to define your inputs. You can read about it in the Syntax Reference manual accessible from the Help menu.

HTH, Jon Peck

answered Jan 9, 2012 at 12:57

JKP

5,41715 silver badges5 bronze badges

2 Comments

djhurio Over a year ago

FILE HANDLE will not handle variable names in GET DATA.

JKP Over a year ago

FILE HANDLE is for abstracting the file name/location.

Collectives™ on Stack Overflow

SPSS Macro to open CSV from variable

2 Answers 2

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related