SPSSX Discussion

Capturing metadata from one file to use on other files

Classic

List

Threaded

9 messages Options

John F Hall

Capturing metadata from one file to use on other files

Please bear with this rather long request.

The British Social Attitudes Survey (BSAS) has been conducted by the National Centre for Social Research (Natcen) almost every year from 1983 to 2016. Once the reports are published the SPSS *.sav files for each year are made available and distributed by the UK Data Service (UKDS). These files are mutually incompatible as to metadata and contain various anomalies: they cannot be merged to allow analysis across years. Over the last four years I have generated a massive cumulative SPSS "mother" *.sav file (1.20gb, 102900 cases, 11588 variables) which includes all waves of the survey, but I now face problems of making it available to fellow researchers and teachers.

Because the data are protected, I cannot make this file or any segments of it available from my website. Natcen and UKDS have neither time nor finance to upload the mother file to the UKDS data archive. They have instead suggested posting the syntax used to generate the mother file, but this runs to dozens of *.sps files, thousands of lines of complex SPSS syntax, Python code provided by Jon Peck and, for some operations, switching data between the Data editor and Excel. Even if I were able to do as asked, I doubt if more than a handful of BSAS users would be capable of understanding and executing them. What they are suggestion is effectively impossible, and in my view unreasonable. The surveys are, after all conducted, using public funds.

I did think about choosing a random case for each year and using APPLY DICTIONARY, but even this would involve publiching actual data. As a way out of this impasse, is there any way that I can generate a *.sps file by stripping off the metadata from the mother file? Users could then download the mutually incompatible files for each year and run the new syntax to make the metadata mutually compatible as to data type, width, format, level, variable labels, value labels and missing values. They would only have to do this 35 times (!) to generate a new set of mutually compatible files.

You guys are bound to have the answer: you're all wonderful. Thanks in advance

John F Hall MA (Cantab) Dip Ed (Dunelm)

[Retired academic survey researcher]

Email: [hidden email]

Website: Journeys in Survey Research

Course: Survey Analysis Workshop (SPSS)

Research: Subjective Social Indicators (Quality of Life)

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

Re: Capturing metadata from one file to use on other files

Administrator

Hello John. You wrote:

John F Hall wrote
> They have instead suggested posting the syntax used to generate the mother
> file, but this runs to dozens of *.sps files, thousands of lines of
> complex SPSS syntax, Python code provided by Jon Peck and, for some
> operations, switching data between the Data editor and Excel. Even if I
> were able to do as asked, I doubt if more than a handful of BSAS users
> would be capable of understanding and executing them."

Are you suggesting that one cannot simply execute a series of syntax files
in sequence because hands-on operations are required at various points? If
so, what exactly are the hands-on operations, and why can they not be
implemented in syntax?

If that is not what you mean, and one can execute a series of syntax files
with no hands-on intervention, then I don't understand what the problem is.
To tidy things up a bit, you could write a master syntax file that includes
a bunch of INSERT commands* to call the other syntax files in the proper
sequence.

* I almost wrote INCLUDE commands, but corrected myself before Jon could
correct me. ;-)

John F Hall wrote
> Please bear with this rather long request.
>
> --- snip ---
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

> LISTSERV@.UGA

> (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
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

=====================
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
bweaver@lakeheadu.ca
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

PLEASE NOTE THE FOLLOWING:
1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above.
2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/).

Jon Peck

Re: Capturing metadata from one file to use on other files

In reply to this post by John F Hall

You can delete all the cases from a sav file and thus save it with only the metadata. Then APPLY DICTIONARY can be used with that as a template.

On Wed, Sep 26, 2018 at 5:24 AM John F Hall <[hidden email]> wrote:

Please bear with this rather long request.

The British Social Attitudes Survey (BSAS) has been conducted by the National Centre for Social Research (Natcen) almost every year from 1983 to 2016. Once the reports are published the SPSS *.sav files for each year are made available and distributed by the UK Data Service (UKDS).   These files are mutually incompatible as to metadata and contain various anomalies: they cannot be merged to allow analysis across years. Over the last four years I have generated a massive cumulative SPSS "mother" *.sav file (1.20gb, 102900 cases, 11588 variables) which includes all waves of the survey, but I now face problems of making it available to fellow researchers and teachers.

Because the data are protected, I cannot make this file or any segments of it available from my website. Natcen and UKDS have neither time nor finance to upload the mother file to the UKDS data archive. They have instead suggested posting the syntax used to generate the mother file, but this runs to dozens of *.sps files, thousands of lines of complex SPSS syntax, Python code provided by Jon Peck and, for some operations, switching data between the Data editor and Excel. Even if I were able to do as asked, I doubt if more than a handful of BSAS users would be capable of understanding and executing them. What they are suggestion is effectively impossible, and in my view unreasonable. The surveys are, after all conducted, using public funds.

I did think about choosing a random case for each year and using APPLY DICTIONARY, but even this would involve publiching actual data. As a way out of this impasse, is there any way that I can generate a *.sps file by stripping off the metadata from the mother file? Users could then download the mutually incompatible files for each year and run the new syntax to make the metadata mutually compatible as to data type, width, format, level, variable labels, value labels and missing values. They would only have to do this 35 times (!) to generate a new set of mutually compatible files.

You guys are bound to have the answer: you're all wonderful. Thanks in advance

John F Hall MA (Cantab) Dip Ed (Dunelm)
[Retired academic survey researcher]

Email:          [hidden email]
Website:     Journeys in Survey Research
Course:       Survey Analysis Workshop (SPSS)
Research:   Subjective Social Indicators (Quality of Life)

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

Jon K Peck
[hidden email]

John F Hall

Re: Capturing metadata from one file to use on other files

Jon

Thanks for this very helpful reply about deleting cases.

I've just tried this on the 2016 data and it works:

GET

FILE='F:\Research\4 Surveys\British Social Attitudes\BSA_Cumulative files\bsa1983-2016mother.sav'.

DATASET NAME DataSet1 WINDOW=FRONT.

GET

FILE='C:\Users\John Hall\Desktop\BSA_2016\SPSS saved files\bsa16_to_ukda.sav'.

DATASET NAME DataSet2 WINDOW=FRONT.

apply dictionary from dataset1

/FILEINFO all

/VARINFO all.

It also gets round the problem of having to keep all 11588 variables from the mother file, keeping only the 822 used in 2016. Having 0 cases will be even easier.

John F Hall MA (Cantab) Dip Ed (Dunelm)

[Retired academic survey researcher]

Email: [hidden email]

Website: Journeys in Survey Research

Course: Survey Analysis Workshop (SPSS)

Research: Subjective Social Indicators (Quality of Life)

From: SPSSX(r) Discussion <[hidden email]> On Behalf Of Jon Peck
Sent: 26 September 2018 15:47
To: [hidden email]
Subject: Re: Capturing metadata from one file to use on other files

You can delete all the cases from a sav file and thus save it with only the metadata. Then APPLY DICTIONARY can be used with that as a template.

On Wed, Sep 26, 2018 at 5:24 AM John F Hall <[hidden email]> wrote:

Please bear with this rather long request.

The British Social Attitudes Survey (BSAS) has been conducted by the National Centre for Social Research (Natcen) almost every year from 1983 to 2016. Once the reports are published the SPSS *.sav files for each year are made available and distributed by the UK Data Service (UKDS).   These files are mutually incompatible as to metadata and contain various anomalies: they cannot be merged to allow analysis across years. Over the last four years I have generated a massive cumulative SPSS "mother" *.sav file (1.20gb, 102900 cases, 11588 variables) which includes all waves of the survey, but I now face problems of making it available to fellow researchers and teachers.

Because the data are protected, I cannot make this file or any segments of it available from my website. Natcen and UKDS have neither time nor finance to upload the mother file to the UKDS data archive. They have instead suggested posting the syntax used to generate the mother file, but this runs to dozens of *.sps files, thousands of lines of complex SPSS syntax, Python code provided by Jon Peck and, for some operations, switching data between the Data editor and Excel. Even if I were able to do as asked, I doubt if more than a handful of BSAS users would be capable of understanding and executing them. What they are suggestion is effectively impossible, and in my view unreasonable. The surveys are, after all conducted, using public funds.

I did think about choosing a random case for each year and using APPLY DICTIONARY, but even this would involve publiching actual data. As a way out of this impasse, is there any way that I can generate a *.sps file by stripping off the metadata from the mother file? Users could then download the mutually incompatible files for each year and run the new syntax to make the metadata mutually compatible as to data type, width, format, level, variable labels, value labels and missing values. They would only have to do this 35 times (!) to generate a new set of mutually compatible files.

You guys are bound to have the answer: you're all wonderful. Thanks in advance

John F Hall MA (Cantab) Dip Ed (Dunelm)
[Retired academic survey researcher]

Email:          [hidden email]
Website:     Journeys in Survey Research
Course:       Survey Analysis Workshop (SPSS)
Research:   Subjective Social Indicators (Quality of Life)

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

Jon K Peck
[hidden email]

John F Hall

Re: Capturing metadata from one file to use on other files

In reply to this post by Jon Peck

Brilliant! Tests on original data mean compatible versions are saved with only their original variables instead of the 11,588 in the mother file. Thanks Jon.

GET

FILE='F:\Research\4 Surveys\British Social Attitudes\BSA_Cumulative files\bsa1983-2016mother.sav'.

DATASET NAME DataSet1 WINDOW=FRONT.

GET

FILE='C:\Users\John Hall\Desktop\BSA_2016\SPSS saved files\bsa16_to_ukda.sav'.

DATASET NAME DataSet2 WINDOW=FRONT.

SAVE OUTFILE='C:\Users\John Hall\Desktop\BSA_2016\SPSS saved files\bsamother_zerocases.sav'

/COMPRESSED.

GET

FILE='F:\Research\4 Surveys\British Social Attitudes\BSA2011 (Jan 14 version)\UKDA-7237-spss\spss\spss19\bsa11.sav'.

>Warning # 5281. Command name: GET FILE

>SPSS Statistics is running in Unicode encoding mode. This file is encoded in

>a locale-specific (code page) encoding. The defined width of any string

>variables are automatically tripled in order to avoid possible data loss. You

>can use ALTER TYPE to set the width of string variables to the width of the

>longest observed value for each string variable.

DATASET NAME DataSet4 WINDOW=FRONT.

apply dictionary from dataset3

/FILEINFO all

/VARINFO all.

SAVE OUTFILE='C:\Users\John Hall\Desktop\BSA_2016\SPSS saved files\bsa2011(compatible).sav'.

/COMPRESSED.

John F Hall MA (Cantab) Dip Ed (Dunelm)

[Retired academic survey researcher]

Email: [hidden email]

Website: Journeys in Survey Research

Course: Survey Analysis Workshop (SPSS)

Research: Subjective Social Indicators (Quality of Life)

You can delete all the cases from a sav file and thus save it with only the metadata. Then APPLY DICTIONARY can be used with that as a template.

On Wed, Sep 26, 2018 at 5:24 AM John F Hall <[hidden email]> wrote:

Please bear with this rather long request.

The British Social Attitudes Survey (BSAS) has been conducted by the National Centre for Social Research (Natcen) almost every year from 1983 to 2016. Once the reports are published the SPSS *.sav files for each year are made available and distributed by the UK Data Service (UKDS).   These files are mutually incompatible as to metadata and contain various anomalies: they cannot be merged to allow analysis across years. Over the last four years I have generated a massive cumulative SPSS "mother" *.sav file (1.20gb, 102900 cases, 11588 variables) which includes all waves of the survey, but I now face problems of making it available to fellow researchers and teachers.

Because the data are protected, I cannot make this file or any segments of it available from my website. Natcen and UKDS have neither time nor finance to upload the mother file to the UKDS data archive. They have instead suggested posting the syntax used to generate the mother file, but this runs to dozens of *.sps files, thousands of lines of complex SPSS syntax, Python code provided by Jon Peck and, for some operations, switching data between the Data editor and Excel. Even if I were able to do as asked, I doubt if more than a handful of BSAS users would be capable of understanding and executing them. What they are suggestion is effectively impossible, and in my view unreasonable. The surveys are, after all conducted, using public funds.

I did think about choosing a random case for each year and using APPLY DICTIONARY, but even this would involve publiching actual data. As a way out of this impasse, is there any way that I can generate a *.sps file by stripping off the metadata from the mother file? Users could then download the mutually incompatible files for each year and run the new syntax to make the metadata mutually compatible as to data type, width, format, level, variable labels, value labels and missing values. They would only have to do this 35 times (!) to generate a new set of mutually compatible files.

You guys are bound to have the answer: you're all wonderful. Thanks in advance

John F Hall MA (Cantab) Dip Ed (Dunelm)
[Retired academic survey researcher]

Email:          [hidden email]
Website:     Journeys in Survey Research
Course:       Survey Analysis Workshop (SPSS)
Research:   Subjective Social Indicators (Quality of Life)

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

Jon K Peck
[hidden email]

David Marso-2

Re: Capturing metadata from one file to use on other files

In reply to this post by John F Hall

Now you can roll up a macro or PROCESS FILES loop to do all of the files.
=====================
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

Jon Peck

Re: Capturing metadata from one file to use on other files

As David hinted, you can run through all the files in one go by using SPSSINC PROCESS FILES. Here is an example assuming that the input sav files are all in a directory named c:\input, and the modified files should go in c:\output, and the master template and syntax files to use are c:\template\master.sav c:\template\apply.sps. It would process all sav files in that directory by executing the syntax in apply.sps on each.

spssinc process files inputdata="c:\input\*.sav" outputdatadir="c:\output"

syntax="c:\template\apply.sps".

Then apply.sps would look like

get file="job_inputfile".

apply dictionary ...

The file after apply dictionary will be written to the specified outputdatadir automatically and closed.

You can put whatever other syntax you want in the syntax file. PROCESS FILES has other options for handling output for each file, error handling etc.

On Wed, Sep 26, 2018 at 1:57 PM David Marso <[hidden email]> wrote:

Now you can roll up a macro or PROCESS FILES loop to do all of the files.
=====================
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

Jon K Peck
[hidden email]

John F Hall

Re: Capturing metadata from one file to use on other files

Jon

Thanks for the suggestion, but writing macros is way over my head. I'm just a humble survey researcher who knows what he needs, and can always find clunky ways of getting it in SPSS. I get the bit about the input and output directories and follow the logic of

GET FILE …

APPLY DICTIONARY.

..and would like to have a shot at it myself.

There will be 35 files to process: bsa1983.sav to bsa2016.sav. It doesn't matter about the names of the output files but it would be useful if they could be something like bsanew1983.sav to bsanew2016.sav. Assuming users will have downloaded the original files (whose filenames don't actually follow the convention above)

1: Create 2 new folders C: bsainput and C: bsaoutput

2: Copy files bsa1983.sav bsa1964.sav ……bsa2016.sav to folder C: bsainput

3: Syntax something like:

GET

FILE='C:\Users\John Hall\Desktop\BSA Compatible Files\bsamother_zerocases.sav'.

DATASET NAME DataSet1 WINDOW=FRONT.

spssinc process files inputdata="c:\input\*.sav" outputdatadir="c:\output*.sav"

syntax="c:\template\apply.sps".

get file="job_inputfile".

apply dictionary from dataset1.

save out "*new.sav".

Will this save the files with a new name, but with the same year? If it works I can post the syntax and instructions to my site, then UKDS will provide a link to it. I'll make a start whilst waiting for a reply. Thanks in advance.

John

John F Hall MA (Cantab) Dip Ed (Dunelm)

[Retired academic survey researcher]

Email: [hidden email]

Website: Journeys in Survey Research

Course: Survey Analysis Workshop (SPSS)

Research: Subjective Social Indicators (Quality of Life)

From: Jon Peck <[hidden email]>
Sent: 26 September 2018 22:24
To: SPSS List <[hidden email]>; John F Hall <[hidden email]>
Subject: Re: [SPSSX-L] Capturing metadata from one file to use on other files

spssinc process files inputdata="c:\input\*.sav" outputdatadir="c:\output"

syntax="c:\template\apply.sps".

Then apply.sps would look like

get file="job_inputfile".

apply dictionary ...

The file after apply dictionary will be written to the specified outputdatadir automatically and closed.

You can put whatever other syntax you want in the syntax file. PROCESS FILES has other options for handling output for each file, error handling etc.

On Wed, Sep 26, 2018 at 1:57 PM David Marso <[hidden email]> wrote:

Now you can roll up a macro or PROCESS FILES loop to do all of the files.
=====================
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

Jon K Peck
[hidden email]

John F Hall

Re: Capturing metadata from one file to use on other files

In reply to this post by Jon Peck

Progress so far. Added all data sets to project. They arrived in individual *.zip files. Great to discover that they can all be unpacked with a single 7-zip "Extract here" click, to yield 35 folders, one for each wave, named:

UKDA-<SN number>-spss, eg UKDA-1935-spss.

Don't know if I can use a single command to extract the folder spss and from that the spss<release> folder, eg spss12, and from that the *.sav file, eg bsa83.sav.

Various releases of SPSS from 12 to 19, none later.

None of the data sets contain a variable indicating wave or year of survey. This has to be added manually. Later waves carry a unique serial number for each case, but earlier waves do not.

[19:40]

Much more progress today.

I'm working on a new page which will say:

To download and merge individual year files from UKDS you will have to be a registered and authorised user.

The original files are not mutually compatible as to metadata. Variables may have the same names, but are stored as various data types, and with a range of different formats, widths, measurement levels, variable labels, value labels and missing value specifications.

To merge the files, they first have to be made compatible. Because the data are protected, no data files can be uploaded to this site. A solution suggested by Natcen and UKDS is that I post the syntax used to create the cumulative SPSS "mother" file, but this ran to dozens of SPSS *.sps files containing hundreds of lines of syntax, some complex Python code and, for some tasks, dozens of switches between the SPSS Data Editor and Excel.

An alternative and much simpler solution, which meets data safeguarding constraints, has been to create a mother file containing no cases (attached) and use the SPSS command APPLY DICTIONARY to create mutually compatible files for each year. For example, using the 1985 survey:

GET

FILE='C:\Users\John Hall\Desktop\input\bsa1985.sav'.

apply dictionary from dataset2

/FILEINFO all

/VARINFO all.

SAVE OUTFILE='C:\Users\John Hall\Desktop\output\bsa1985(compatible).sav'

/COMPRESSED.

Users will have to download the source files from UKDS and process them one at a time (preferably in reverse year order) using the following table [not included here as embedded links]

Will notify of new page with instructions for downloading and processing files.

John F Hall MA (Cantab) Dip Ed (Dunelm)

[Retired academic survey researcher]

Email: [hidden email]

Website: Journeys in Survey Research

Course: Survey Analysis Workshop (SPSS)

Research: Subjective Social Indicators (Quality of Life)

From: John F Hall <[hidden email]>
Sent: 27 September 2018 07:53
To: 'Jon Peck' <[hidden email]>; 'SPSS List' <[hidden email]>
Cc: 'bweaver' <[hidden email]>
Subject: RE: [SPSSX-L] Capturing metadata from one file to use on other files

Jon

GET FILE …

APPLY DICTIONARY.

..and would like to have a shot at it myself.

1: Create 2 new folders C: bsainput and C: bsaoutput

2: Copy files bsa1983.sav bsa1964.sav ……bsa2016.sav to folder C: bsainput

3: Syntax something like:

GET

FILE='C:\Users\John Hall\Desktop\BSA Compatible Files\bsamother_zerocases.sav'.

DATASET NAME DataSet1 WINDOW=FRONT.

spssinc process files inputdata="c:\input\*.sav" outputdatadir="c:\output*.sav"

syntax="c:\template\apply.sps".

get file="job_inputfile".

apply dictionary from dataset1.

save out "*new.sav".

John

John F Hall MA (Cantab) Dip Ed (Dunelm)

[Retired academic survey researcher]

Email: [hidden email]

Website: Journeys in Survey Research

Course: Survey Analysis Workshop (SPSS)

Research: Subjective Social Indicators (Quality of Life)

spssinc process files inputdata="c:\input\*.sav" outputdatadir="c:\output"

syntax="c:\template\apply.sps".

Then apply.sps would look like

get file="job_inputfile".

apply dictionary ...

The file after apply dictionary will be written to the specified outputdatadir automatically and closed.

You can put whatever other syntax you want in the syntax file. PROCESS FILES has other options for handling output for each file, error handling etc.

On Wed, Sep 26, 2018 at 1:57 PM David Marso <[hidden email]> wrote:

Now you can roll up a macro or PROCESS FILES loop to do all of the files.
=====================
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

Jon K Peck
[hidden email]