How to use two different excel files in same syntax procedure?

Question

I have an excel file with information about variables (excel1) and another one with information about lists (excel2).

In order to create a syntax to generate a new syntax to create VARIABLE and VALUES LABELS, I used solution proposed by @eli.k here. But with this solution I have to have a dataset with lists so I could use it instead of writing it “by hand” (copy/paste) (here). One problem came with L2, which has 195 entries so the new create variable would need to be bigger that 20.000 characters (is this possible in SPSS?), appearing all in one line.

What I want to know is if it’s possible to use excel2 automatically in code, line by line. Using the following code:

GET DATA
  /TYPE=XLSX
  /FILE=" D:\excel1.xlsx "
  /SHEET=name 'Folha1'
  /CELLRANGE=FULL
  /READNAMES=ON
  /DATATYPEMIN PERCENTAGE=95.0.

STRING cmd1 cmd2 (a200).
SORT CASES by List.
MATCH FILES /FILE=* /FIRST=first /LAST=last /BY List. /* marking first and last lines.
DO IF first.
    COMPUTE cmd1="VARIABLE LABELS".
    COMPUTE cmd2="VALUE LABELS".
END IF.
IF not first cmd1=concat(rtrim(cmd1), " "). /* "/" only appears from the second varname.
COMPUTE cmd1=concat(rtrim(cmd1), " ", Var_label).
COMPUTE cmd2=concat(rtrim(cmd2), " ", Var).
DO IF last.
    COMPUTE cmd1=concat(rtrim(cmd1), ".").
    COMPUTE cmd2=concat(rtrim(cmd2), " ",' 1 "Afghanistan"  2 "Albania" (…) 195 "Zimbabwe".').
END IF.
EXECUTE.

SELECT IF ('List' 'L2').
ADD FILES /file=* /rename cmd1=cmd /file=* /rename cmd2=cmd.
EXECUTE.

I would like to know if there is a way to replace ' 1 "Afghanistan" 2 "Albania" (…) 195 "Zimbabwe".'' by some function/procedure to grab information from excel2 concerning L2, and showing it line by line:

(…)

VARIABLE LABELS V2 "Country"                                                                                                                                                                                
 / V3 "Country Mother"
 / V4 "Country Father".  

VALUE LABELS V2
V3
V4
1 "Afghanistan"
2 "Albania"
(…)
195 "Zimbabwe".

Thanks for helping me!

Answer 1

This issue is pretty complex and would usually be beyond the scope of Stack-Overflow Q&A but here's my answer anyway:

First I recreate the parts of your example data concerning the value labels only:

data list list/var list (2a5).
begin data
    "v1" "L1"
    "v2" "L2"
    "v3" "L2"
    "v4" "L2"
end data.
dataset name xl1.


data list list/list (a5) nb (f5) nb_txt (a20).
begin data
"L1" 1 "Female"
"L1" 2 "Male"
"L2" 1  "Afghanistan"
"L2" 2 "Albania"
"L2" 43 "Israel"
"L2" 195 "Zimbabwe"
end data.
dataset name xl2.

data list list/v1 v2 v3 v4 (4f3).
begin data
    1 1 2 3 
    2 2 2 43
    1 2 1 195
end data.
dataset name gen.

Now to work:

The first part is to create a macro for each list of variable labels. since some of the lists are long, I use ADD Value labels separately for each value.

dataset activate xl2.
string cmd  (a200) cmdFin (a20).
sort cases by list nb.
match files /file=* /by list /first=first /last=last.
compute cmd=concat("add value labels !1 ", string(nb,f6), " '", rtrim(nb_txt), "' .").
if first cmd=concat("define dolist_", list, " (!pos=!cmdend)     ", rtrim(cmd)).
if last cmdFin=" !enddefine .".
write outfile="path\create value label macros.sps"/cmd/cmdfin.
exe.
insert file="path\create value label macros.sps".

After inserting the generated syntax a macro has been defined for each of the value lists. Now we create an additional syntax that will run the related macro for each of the variable names in the list:

dataset activate xl1.
string cmd (a200).
compute cmd=concat("dolist_", list, " ", var, " .").
write outfile="path\run value label macros.sps"/cmd.
exe.

Now we can actually try out the generated macros on our original data:

dataset activate gen.
insert file="path\run value label macros.sps".