Automated compatibility and merging of files

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Automated compatibility and merging of files

John F Hall
Automated compatibility and merging of files

There are 35 waves of the British Social Attitudes Survey (BSAS).  Variables with the same name used in different years have different specifications for formats, missing values, levels etc.  The metadata in the original files are not compatible between years and need to be modified so the files can be merged for analysis across time.  I have created a cumulative SPSS "mother" in which the metadata for all variables is now compatible.  It contains all variables used in all surveys 1983 to 2017 (no survey 1988 or 1992) but in reverse year order.

The General Data Protection Regulations (GDPR) mean that I cannot upload actual data to my site.  Until Natcen and UKDS decide whether to use my mother file, users wishing to perform analysis across time must download the individual year files from UKDS and fend for themselves.  However I have been asked to supply the syntax needed to make files mutually compatible and then merge them.  Doing this piecemeal for any combination of files would be tedious, but I have generated some test syntax for the 2016 and 2017 files.

Desktop older BSAS_Merge_files  contains three files:

bsamother_ zerocases _ sav      (JKP's brilliant suggestion to get round General Data Protection   Regulations: the file contains no cases)

bsa16_to_ukda.sav               (Original data for 2016: no variable year)

bsa2017_for_ukda.sav            (Original data for 2017: no variable year)

Proceed as follows:

Double click on bsamother_zerocases.sav

bsamother_zerocases.sav becomes [Dataset1]

Double click on bsa16_to_ukda.sav

bsa2016_to_ukda.sav becomes [Dataset2]

Double click on bsa2017_for_ukda.sav

bsa2017_for_ukda.sav becomes [Dataset3]

I would like to know if there is way to automate the process in the following syntax. 

Syntax file 20172016merge2b.sps

* Encoding: UTF-8.

title 'Using double click '.

*Source files are in desktop folder BSAS_Merge_files.

*In folder BSAS_Merge_files, double click bsamother_zerocases.sav.

*bsamother_zerocases.sav becomes dataset1.

*In folder BSAS_Merge_files, double click bsa16_to_ukda.sav.

* bsa16_to_ukda.sav becomes dataset2.

COMPUTE year = 2016.

APPLY DICTIONARY FROM dataset1

    /FILEINFO all

    /VARINFO all.

SAVE OUTFILE='C:\Users\John Hall\Desktop\BSAS_Merge_files\bsa2016.sav'

    /keep year all

  /COMPRESSED.

*File bsa2016.sav is written to folder BSAS_Merge_files.

*In folder BSAS_Merge_files, double click bsa2017_for_ukda.sav.

*File bsa2017_for_ukda.sav becomes dataset3.

COMPUTE year = 2017.

APPLY DICTIONARY FROM dataset1

    /FILEINFO all

    /VARINFO all.

SAVE OUTFILE='C:\Users\John Hall\Desktop\BSAS_Merge_files\bsa2017.sav'

  /keep year all

  /COMPRESSED.

*File bsa2017.sav is written to folder BSAS_Merge_files.

*In folder BSAS_Merge_files, double click bsa2016.sav.

* bsa2016.sav becomes dataset4.

*In folder BSAS_Merge_files, double click bsa2017.sav.

* bsa2017.sav becomes dataset5.

ADD FILES file dataset4  /file dataset5.

*Data saved to temporary file Untitled [].

SAVE OUTFILE='C:\Users\John Hall\Desktop\BSAS_Merge_files\bsa2017_2016b.sav'

     /keep year all.

*File bsa2017_2016b.sav is saved to folder BSAS_Merge_files.

My question is can this process be automated (in a macro?) so that users can supply lists of:

a)      Original filenames (see list below)

b)      Matching compatible user-supplied filenames (after APPLY DICTIONARY )

c)      File combining two or more matching files (after ADD FILES)

The original 35 filenames for download from UKDS are:

bsa83.sav      
bsa84.sav      
bsa85.sav      
2315.sav       
bsa87.sav      
bsa89.sav, 2723.sav    
bsa90.por      
f952.sav       
bsa93.sav      
bsa94.sav      
bsa95.sav      
g921au.sav, g921bu.sav 
97.por, bsa97a.por     
bsa98a.sav     
bsa99a.sav     
bsa00.por, bsa00.sav   
bsa01.por, bsa01soc.por
bsa02.por      
bsa03.por      
bsa04.sav      
bsa05.sav      
bsa06.sav      
bsa07.sav      
bsa08.sav      
bsa09.sav      
bsa10.sav      
bsa11.sav      
bsa12.sav      
bsa13.sav bsa13ukds.sav
bsa14ukds.sav bsa14_final.sav  
bsa15_to_ukds_final.sav
bsa16_to_ukda.sav      
bsa2017_for_ukda       

Sorry I can't share the data, but I hope this makes sense.

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