Merging multiple challenging files

[Manmit Shrimali-4]

Thanks David for pointing my error. Here is my query.

Jon, I have SPSS v20, thank you for sharing the extension, I will check it out right away. So if I understand correctly, for numeric variables, even if VAR1 is f30 in file 1 and F40 in file 2, they will still merge in same variable? Also, if all 10 file have different length, which length it will retain? I also want to get list of all the variables across 10 files as after appending the files, I need to run var lab, val lab, and recode commands. Is there a way to first get master list of all the variables across 10 files with source?

***original query*****

Friends,

I have been using SPSS for a while along with Syntax but just couldn't figure out easy solution to following issue:

Issue: I need to APPEND or add 10+ data files. Files do share few common variables but each file also has new variables. For e.g., file 1 has 10 common variables but 30 new variables only unique to this file.

I can easily add the files but the issue is that each file has different number format for common variables. For e.g., VAR1 is F30 in file 1 but F40 in file 2 and so on.

Objective:
1. Create long list of variables that covers all variables across 10 files. THis will be master long list of variables
2. Get maximum value of column width and apply to all common variables across files. For e.g., if VAR1 is F30 in file 1 and f40 in file 2 then file 1 VAR1's should be replaced as F40. Currently, I am doing this in excel by comparing data dictionary of all 10 files and it is soul destroying process.
3. Once variable format is same across files, I would then append all the files in one go.

I tried to find solution on spss tools net but couldn't find anything, except comparing two datasets which really doesn't solve my problem.

Any help is greatly appreciated - Jon P, I would appreciate if you can share your expertise here.

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

Numeric variables are always stored as double precision floating point numbers regardless of the format.  You will not lose anything if the formats differ, and that will not affect the merge.

After the merge, the variable list will be all the file 1 variables, then additional variables from file 2 and so on, assuming you are not using any drop or keep subcommands.  Variables will have the meta data based on where the variables were first found, so I'm not clear on what more you want to do.  I suggest that you do the merge and then report what additional metadata manipulation you need to do.  If you need to do source-specific transformations, perhaps you should do those in the files before merging.

The MAP subcommand of ADD FILES or MATCH FILES produces a table listing all the variables and their source.

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: [phone]




From:        Manmit Shrimali <[hidden email]>
To:        [hidden email],
Date:        08/08/2013 07:14 AM
Subject:        [SPSSX-L] Merging multiple challenging files
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Thanks David for pointing my error. Here is my query.

Jon, I have SPSS v20, thank you for sharing the extension, I will check it out right away. So if I understand correctly, for numeric variables, even if VAR1 is f30 in file 1 and F40 in file 2, they will still merge in same variable? Also, if all 10 file have different length, which length it will retain? I also want to get list of all the variables across 10 files as after appending the files, I need to run var lab, val lab, and recode commands. Is there a way to first get master list of all the variables across 10 files with source?

***original query*****

Friends,

I have been using SPSS for a while along with Syntax but just couldn't figure out easy solution to following issue:

Issue: I need to APPEND or add 10+ data files. Files do share few common variables but each file also has new variables. For e.g., file 1 has 10 common variables but 30 new variables only unique to this file.

I can easily add the files but the issue is that each file has different number format for common variables. For e.g., VAR1 is F30 in file 1 but F40 in file 2 and so on.

Objective:
1. Create long list of variables that covers all variables across 10 files. THis will be master long list of variables
2. Get maximum value of column width and apply to all common variables across files. For e.g., if VAR1 is F30 in file 1 and f40 in file 2 then file 1 VAR1's should be replaced as F40. Currently, I am doing this in excel by comparing data dictionary of all 10 files and it is soul destroying process.
3. Once variable format is same across files, I would then append all the files in one go.

I tried to find solution on spss tools net but couldn't find anything, except comparing two datasets which really doesn't solve my problem.

Any help is greatly appreciated - Jon P, I would appreciate if you can share your expertise here.

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

[Manmit Shrimali-4]

Thanks Jon. I treid the adjust width extention. I opend file1 and then clicked on adjust width under data and in browse file, I chose file2 while pulling all the string variables. After running it, I got a warning in the output window: max() arg empty. Syntax ran, it opened new file file2_adjust but string width did not change. Both original file1 and file2 still have string variables with different values. If it helps, I can send you my datafiles.  

On Thu, Aug 8, 2013 at 9:31 AM, Jon K Peck <[hidden email]> wrote:

Numeric variables are always stored as double precision floating point numbers regardless of the format.  You will not lose anything if the formats differ, and that will not affect the merge.

After the merge, the variable list will be all the file 1 variables, then additional variables from file 2 and so on, assuming you are not using any drop or keep subcommands.  Variables will have the meta data based on where the variables were first found, so I'm not clear on what more you want to do.  I suggest that you do the merge and then report what additional metadata manipulation you need to do.  If you need to do source-specific transformations, perhaps you should do those in the files before merging.

The MAP subcommand of ADD FILES or MATCH FILES produces a table listing all the variables and their source.

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: <a href="tel:[phone]" value="[phone]" target="_blank">720-342-5621




From:        Manmit Shrimali <[hidden email]>
To:        [hidden email],
Date:        08/08/2013 07:14 AM
Subject:        [SPSSX-L] Merging multiple challenging files
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Thanks David for pointing my error. Here is my query.

Jon, I have SPSS v20, thank you for sharing the extension, I will check it out right away. So if I understand correctly, for numeric variables, even if VAR1 is f30 in file 1 and F40 in file 2, they will still merge in same variable? Also, if all 10 file have different length, which length it will retain? I also want to get list of all the variables across 10 files as after appending the files, I need to run var lab, val lab, and recode commands. Is there a way to first get master list of all the variables across 10 files with source?

***original query*****

Friends,

I have been using SPSS for a while along with Syntax but just couldn't figure out easy solution to following issue:

Issue: I need to APPEND or add 10+ data files. Files do share few common variables but each file also has new variables. For e.g., file 1 has 10 common variables but 30 new variables only unique to this file.

I can easily add the files but the issue is that each file has different number format for common variables. For e.g., VAR1 is F30 in file 1 but F40 in file 2 and so on.

Objective:
1. Create long list of variables that covers all variables across 10 files. THis will be master long list of variables
2. Get maximum value of column width and apply to all common variables across files. For e.g., if VAR1 is F30 in file 1 and f40 in file 2 then file 1 VAR1's should be replaced as F40. Currently, I am doing this in excel by comparing data dictionary of all 10 files and it is soul destroying process.
3. Once variable format is same across files, I would then append all the files in one go.

I tried to find solution on spss tools net but couldn't find anything, except comparing two datasets which really doesn't solve my problem.

Any help is greatly appreciated - Jon P, I would appreciate if you can share your expertise here.

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

[MR]

All,

This is resolved. Thanks to Jon for updating the ADJUST WIDTH extension.

Thank you all.

Manmit
On 2013-08-07, at 10:19 PM, Manmit Shrimali <[hidden email]> wrote:

> Thanks David for pointing my error. Here is my query.
>
> Jon, I have SPSS v20, thank you for sharing the extension, I will check it out right away. So if I understand correctly, for numeric variables, even if VAR1 is f30 in file 1 and F40 in file 2, they will still merge in same variable? Also, if all 10 file have different length, which length it will retain? I also want to get list of all the variables across 10 files as after appending the files, I need to run var lab, val lab, and recode commands. Is there a way to first get master list of all the variables across 10 files with source?
>
> ***original query*****
>
> Friends,
>
> I have been using SPSS for a while along with Syntax but just couldn't figure out easy solution to following issue:
>
> Issue: I need to APPEND or add 10+ data files. Files do share few common variables but each file also has new variables. For e.g., file 1 has 10 common variables but 30 new variables only unique to this file.
>
> I can easily add the files but the issue is that each file has different number format for common variables. For e.g., VAR1 is F30 in file 1 but F40 in file 2 and so on.
>
> Objective:
> 1. Create long list of variables that covers all variables across 10 files. THis will be master long list of variables
> 2. Get maximum value of column width and apply to all common variables across files. For e.g., if VAR1 is F30 in file 1 and f40 in file 2 then file 1 VAR1's should be replaced as F40. Currently, I am doing this in excel by comparing data dictionary of all 10 files and it is soul destroying process.
> 3. Once variable format is same across files, I would then append all the files in one go.
>
> I tried to find solution on spss tools net but couldn't find anything, except comparing two datasets which really doesn't solve my problem.
>
> Any help is greatly appreciated - Jon P, I would appreciate if you can share your expertise here.
>
> =====================
> 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

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