Apply frequency table to recode a value

[rprimi]

Dear list members

I have a table of cumulative frequencies for values calculated in one file (see bellow Var1 that range from from -4.73 to 4.83). Now, in a new file, I want to recode var2, that is in the same scale of Var1, applying the cumulative percent based on this table. For instance if a value of Var2 is -2.11 it would be recoded to 1.4 ; if -1.80 to 3.2 and so forth … Any idea of a easy way to do that ?

Thanks
Ricardo Primi

Var1            Cum Freq.
-4.736          .0
-3.519          .1
-2.744          .4
-2.259          1.4
-1.892          3.2
-1.588          6.2
-1.322          10.5
-1.081          16.2
-.857            23.0
-.644            31.1
-.438            39.8
-.235            49.1
-.034            58.6
.169             67.5
.376             76.0
.590             83.4
.814             89.4
1.052           94.0
1.309           97.0
1.594           98.8
1.919           99.6
2.308           99.9
2.816           100.0
3.613           100.0
4.853           100.0

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

[Maguin, Eugene]

Ricardo,

I don't understand what you are trying to do. I understand that you have
data on the same scale, assumed to be a set of items, from two different
groups, file1 and file2. So you do frequencies on file1-var1. Depending on
how similar the two groups are, scores for the second group, which you are
calling 'var2', will be more or less similar in distribution but maybe not
exact values. So suppose the file1-var1 and file2-var2 distributions are

Var1
Value   Count   Percent   Cum Percent
1.262     8       8.0         8.0
1.579     6       6.0        14.0
1.903    10      10.0        24.0
2.345     4       4.0        28.0
2.674    12      12.0        40.0
2.888    13      13.0        53.0
3.002    19      19.0        72.0
3.451    19      19.0        91.0
3.897     9       9.0       100.0
        ----     ----
        100      100


Var2
Value   Count   Percent   Cum Percent
1.362     4       4.0         4.0
1.779     1       1.0         5.0
2.003    15      15.0        20.0
2.345    13      13.0        33.0
2.574     9       9.0        42.0
2.898    27      27.0        69.0
3.106    11      11.0        80.0
3.651    13      13.0        93.0
3.800     7       7.0       100.0
        ----     ----
        100      100

Tell us how you are going to convert var2 to var1.

Gene Maguin







-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Ricardo Primi
Sent: Sunday, September 11, 2011 7:17 PM
To: [hidden email]
Subject: Apply frequency table to recode a value

Dear list members

I have a table of cumulative frequencies for values calculated in one file
(see below Var1 that range from from -4.73 to 4.83). Now, in a new file, I
want to recode var2, that is in the same scale of Var1, applying the
cumulative percent based on this table. For instance if a value of Var2 is
-2.11 it would be recoded to 1.4 ; if -1.80 to 3.2 and so forth . Any idea
of a easy way to do that ?

Thanks
Ricardo Primi

Var1            Cum Freq.
-4.736          .0
-3.519          .1
-2.744          .4
-2.259          1.4
-1.892          3.2
-1.588          6.2
-1.322          10.5
-1.081          16.2
-.857            23.0
-.644            31.1
-.438            39.8
-.235            49.1
-.034            58.6
.169             67.5
.376             76.0
.590             83.4
.814             89.4
1.052           94.0
1.309           97.0
1.594           98.8
1.919           99.6
2.308           99.9
2.816           100.0
3.613           100.0
4.853           100.0

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

[Ruben Geert van den Berg]

Dear Ricardo,

If I understand correctly, you can use a match files command which specifies a file and a table. Based upon the values of some linking variable (in your case var1 = var2), the cumulative frequencies will be added to the data file. This is comparable to a left outer join in SQL or a vertical lookup in EXCEL. 

The file in which each value of the matching variable occurs only once must be specified as table in the match files command and the file containing duplicates as file. Furthermore, the name of the matching variable in the table must be the same as in the file and both files must be sorted ascendingly according to this variable. So with some sort cases, rename variables and a match files commands you should get there. 

If I misunderstood or you need an example, please let us know.

HTH,

Ruben

> Date: Sun, 11 Sep 2011 20:16:32 -0300

> From: [hidden email]
> Subject: Apply frequency table to recode a value
> To: [hidden email]
>
> Dear list members
>
> I have a table of cumulative frequencies for values calculated in one file (see bellow Var1 that range from from -4.73 to 4.83). Now, in a new file, I want to recode var2, that is in the same scale of Var1, applying the cumulative percent based on this table. For instance if a value of Var2 is -2.11 it would be recoded to 1.4 ; if -1.80 to 3.2 and so forth … Any idea of a easy way to do that ?
>
> Thanks
> Ricardo Primi
>
> Var1 Cum Freq.
> -4.736 .0
> -3.519 .1
> -2.744 .4
> -2.259 1.4
> -1.892 3.2
> -1.588 6.2
> -1.322 10.5
> -1.081 16.2
> -.857 23.0
> -.644 31.1
> -.438 39.8
> -.235 49.1
> -.034 58.6
> .169 67.5
> .376 76.0
> .590 83.4
> .814 89.4
> 1.052 94.0
> 1.309 97.0
> 1.594 98.8
> 1.919 99.6
> 2.308 99.9
> 2.816 100.0
> 3.613 100.0
> 4.853 100.0
>
> =====================
> 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

[David Marso]

This will work only if the values match exactly (unlikely) and given the example not the case.
Say your table file is called "<valid path/>File1.sav" and the file to be recoded "valid path/>File2.sav".
The "recoded" variable will be called NewVar
Assuming the variable in question has the same name (say varX) in both files and each file is sorted by varX:
<UNTESTED>
ADD FILES
     / FILE = "<valid path/>File1.sav"
     / FILE = "<valid path/>File2.sav"  / IN =TAG1
     / BY varX .
IF TAG1 NewVar=LAG(varX).
HTH, David

Ruben van den Berg wrote

Dear Ricardo,
If I understand correctly, you can use a match files command which specifies a file and a table. Based upon the values of some linking variable (in your case var1 = var2), the cumulative frequencies will be added to the data file. This is comparable to a left outer join in SQL or a vertical lookup in EXCEL.
The file in which each value of the matching variable occurs only once must be specified as table in the match files command and the file containing duplicates as file. Furthermore, the name of the matching variable in the table must be the same as in the file and both files must be sorted ascendingly according to this variable. So with some sort cases, rename variables and a match files commands you should get there.
If I misunderstood or you need an example, please let us know.
HTH,
Ruben




> Date: Sun, 11 Sep 2011 20:16:32 -0300
> From: [hidden email]
> Subject: Apply frequency table to recode a value
> To: [hidden email]
>
> Dear list members
>
> I have a table of cumulative frequencies for values calculated in one file (see bellow Var1 that range from from -4.73 to 4.83). Now, in a new file, I want to recode var2, that is in the same scale of Var1, applying the cumulative percent based on this table. For instance if a value of Var2 is -2.11 it would be recoded to 1.4 ; if -1.80 to 3.2 and so forth … Any idea of a easy way to do that ?
>
> Thanks
> Ricardo Primi
>
> Var1            Cum Freq.
> -4.736          .0
> -3.519          .1
> -2.744          .4
> -2.259          1.4
> -1.892          3.2
> -1.588          6.2
> -1.322          10.5
> -1.081          16.2
> -.857            23.0
> -.644            31.1
> -.438            39.8
> -.235            49.1
> -.034            58.6
> .169             67.5
> .376             76.0
> .590             83.4
> .814             89.4
> 1.052           94.0
> 1.309           97.0
> 1.594           98.8
> 1.919           99.6
> 2.308           99.9
> 2.816           100.0
> 3.613           100.0
> 4.853           100.0
>
> =====================
> 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

[David Marso]

Oops, make that last line:
IF TAG1 NewVar=LAG(CumFreq). where CumFreq is the cumulative frequency variable in the lookup file.

David Marso wrote

This will work only if the values match exactly (unlikely) and given the example not the case.
Say your table file is called "<valid path/>File1.sav" and the file to be recoded "valid path/>File2.sav".
The "recoded" variable will be called NewVar
Assuming the variable in question has the same name (say varX) in both files and each file is sorted by varX:
<UNTESTED>
ADD FILES
     / FILE = "<valid path/>File1.sav"
     / FILE = "<valid path/>File2.sav"  / IN =TAG1
     / BY varX .
IF TAG1 NewVar=LAG(varX).
HTH, David

Ruben van den Berg wrote

Dear Ricardo,
If I understand correctly, you can use a match files command which specifies a file and a table. Based upon the values of some linking variable (in your case var1 = var2), the cumulative frequencies will be added to the data file. This is comparable to a left outer join in SQL or a vertical lookup in EXCEL.
The file in which each value of the matching variable occurs only once must be specified as table in the match files command and the file containing duplicates as file. Furthermore, the name of the matching variable in the table must be the same as in the file and both files must be sorted ascendingly according to this variable. So with some sort cases, rename variables and a match files commands you should get there.
If I misunderstood or you need an example, please let us know.
HTH,
Ruben




> Date: Sun, 11 Sep 2011 20:16:32 -0300
> From: [hidden email]
> Subject: Apply frequency table to recode a value
> To: [hidden email]
>
> Dear list members
>
> I have a table of cumulative frequencies for values calculated in one file (see bellow Var1 that range from from -4.73 to 4.83). Now, in a new file, I want to recode var2, that is in the same scale of Var1, applying the cumulative percent based on this table. For instance if a value of Var2 is -2.11 it would be recoded to 1.4 ; if -1.80 to 3.2 and so forth … Any idea of a easy way to do that ?
>
> Thanks
> Ricardo Primi
>
> Var1            Cum Freq.
> -4.736          .0
> -3.519          .1
> -2.744          .4
> -2.259          1.4
> -1.892          3.2
> -1.588          6.2
> -1.322          10.5
> -1.081          16.2
> -.857            23.0
> -.644            31.1
> -.438            39.8
> -.235            49.1
> -.034            58.6
> .169             67.5
> .376             76.0
> .590             83.4
> .814             89.4
> 1.052           94.0
> 1.309           97.0
> 1.594           98.8
> 1.919           99.6
> 2.308           99.9
> 2.816           100.0
> 3.613           100.0
> 4.853           100.0
>
> =====================
> 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

[rprimi]

Dear Gene, Bruce and David

In datafile 1 I have a national representative sample of students that answered an achievement Test A composed of 24 items. In datafile 2 I have a smaller sample that answered a different test form (Test B) measuring the same construct but composed of 30 items with some common items from Form A. I used IRT to calibrate and equate both forms using the commons items as anchors. Therefore the var 1 (datafile 1) and var 2 (datafile 2) are in the same metric. The percentiles associated to the values of var1 are the normative reference that I want to assign to the values of var2 to compare the relative sanding of datafile 2 students as compared to those in datafile 1.
I am trying to approach this using match tables but one problem is that I don’t have exactly the same values in both files.

I tried David’s suggestion but couldn’t solve yet but I will work more on that ..
Thank you very much for the suggestions .

Ricardo Primi

Ricardo Primi

http://lattes.cnpq.br/5114152079911903 
University of São Francisco, Brazil

[David Marso]

Slight modification of my previous post.
ADD FILES
     / FILE = "<valid path/>File1.sav"
     / FILE = "<valid path/>File2.sav"  / IN =TAG1
     / BY varX .
IF (TAG1 EQ 0) NewVar=CumFreq.
IF (TAG1 EQ 1) NewVar=LAG(NewVar).
HTH, David

rprimi wrote

Dear Gene, Bruce and David

In datafile 1 I have a national representative sample of students that answered an achievement Test A composed of 24 items. In datafile 2 I have a smaller sample that answered a different test form (Test B) measuring the same construct but composed of 30 items with some common items from Form A. I used IRT to calibrate and equate both forms using the commons items as anchors. Therefore the var 1 (datafile 1) and var 2 (datafile 2) are in the same metric. The percentiles associated to the values of var1 are the normative reference that I want to assign to the values of var2 to compare the relative sanding of datafile 2 students as compared to those in datafile 1.
I am trying to approach this using match tables but one problem is that I don’t have exactly the same values in both files.

I tried David’s suggestion but couldn’t solve yet but I will work more on that ..
Thank you very much for the suggestions .

Ricardo Primi

http://lattes.cnpq.br/5114152079911903 
University of São Francisco, Brazil