topic Re: How to convert column value labels to values? in Discussions

How to convert column value labels to values?

jenssjorslev — Fri, 06 Feb 2015 06:30:51 GMT

Hi,

I'm new to the community (but have used JMP since version 1), so I don't know where to put this question.

Have imported a file from SPSS (.sav) with several columns with value labels. I want to use the value labels (which are actual names) instead of the values (which are numbers). Recode seems not to have this function. Could anybody help? Thanks a lot in advance. Jens

Re: How to convert column value labels to values?

stan_koprowski — Tue, 18 Oct 2016 22:31:56 GMT

Hello,

If I am understanding your question correctly.

You can turn the value labels on or off through the column properties.

In this example using the Consumer Preferences from the sample data I was able to right-click on the column and uncheck the value label option.

The column is no longer using the value label as shown below.

I hope this helps.

Best,

Stan

Re: How to convert column value labels to values?

jenssjorslev — Sat, 07 Feb 2015 06:24:01 GMT

Hi Stanley, Thanks for trying to help. I know the function of value labels, but what I need is to change or substitute 1,2,3 values to No, Yes (e.g. as in your example) character values.

I need to join two tables with matching columns one of which has character values (same as the value labels in the other table, i.e., no coding was used), the other has numeric values (codes) with value labels.

Or is there a way to join the tables on the fly based on a column's value labels in one table and actual values in the matching column in the other table?

Best regards

Jens

Re: How to convert column value labels to values?

ms — Sat, 07 Feb 2015 11:30:53 GMT

Change Data Type to character and then use Recode.

Re: How to convert column value labels to values?

jenssjorslev — Tue, 10 Feb 2015 07:25:29 GMT

Hi, thanks for the tip. There more than 300 value labels, which I would have to enter manually in Recode - I hoped there would be a smarter way.

Re: How to convert column value labels to values?

pmroz — Tue, 10 Feb 2015 14:15:20 GMT

If you have a table of the codes and their values you could use the UPDATE command to add the appropriate value column.

Re: How to convert column value labels to values?

KarenC — Tue, 10 Feb 2015 15:55:42 GMT

I think what we would like (but don't have right now) is a "recode with value labels". I too have wanted this feature from time to time.

Karen

Re: How to convert column value labels to values?

XanGregg — Tue, 18 Oct 2016 22:33:25 GMT

Good idea, Karen.

Jens, It's not easy to get the labels out as data. Two ways I can think of:

With scripting, you can use Get Value Labels and manually or programmatically manipulate the result to be like the Match() expression that Recode generates when you save it as a script.
Do a Tabulate on the variable and use Make Into Data Table on the result.

For #2, you'll need some way to match things up if they're not in a sorted one-to-one mapping. Steps for that:

Create a copy of the values in a new column (without Value Labels)
Use Tabulate with both variables as category columns:
Use the Tabulate command Make into Data Table, to get a new table with no Value Labels properties.
Use Tables > Join to match on the raw values and get the new "labeled" column without Value Labels.

Re: How to convert column value labels to values?

ms — Fri, 15 Jun 2018 12:26:19 GMT

I could not resist try to script Karen's idea. Get value Labels return an expression with one argument: a list of "assignments", e.g. 2 = "No". I come to think of two approaches: either use these (illegal) assignments directly or as Xan suggests manipulate them into a Match expression.

Below are my attempts at both approaches. The Match approach is faster here. For large tables and if many values lack labels the performance appears to become more equal. The Match code can probably be be improved.

open_example_table = Expr(
    dt = Open( "$SAMPLE_DATA/CrabSatellites.jmp" );
    col = Column( dt, "color" );
    col << data type( character );
);
 
//1
open_example_table;
bt = HP Time();
// Direct assignment code
v = Arg( col << get value labels(), 1 );
For( i = 1, i <= N Items( v ), i++,
    col[dt << get rows where( col[] == Arg( v[i], 1 ) )] = Arg( v[i], 2 )
);
// End direct assignment
ht = HP Time();
t1 = ht - bt;
Close( dt, no save );
 
// 2
open_example_table;
bt = HP Time();
// code for building Match expression by string manipulation
match_expr = Parse(
    "Match( col[]," || Substitute( Char( Arg( col << get value labels(), 1 ) ),
        "{", "",
        "=", ",",
        "}", ""
    ) || ",col[])"
);
For Each Row( col[] = match_expr );
// End Match
ht = HP Time();
t2 = ht - bt;
 
Show( t1, t2 );

 
t1 = 1729;
t2 = 1019;

//:*/

Re: How to convert column value labels to values?

chungwei — Thu, 12 Feb 2015 18:32:34 GMT

Another way to convert the column to a column of labels, is to copy the column, paste the data into a script editor window (or any text window). Then copy the data from the text window. Paste into a new character column in your original table.

Re: How to convert column value labels to values?

jenssjorslev — Mon, 16 Mar 2015 07:28:11 GMT

Thanks to all who has helped with this. Been away from the computer for some time, but now back to working with JMP. I'll try the different suggestions and see which is the fastest for me.