BookmarkSubscribe
Choose Language Hide Translation Bar
lilysecret
Community Trekker

Recode a series of columns with a loop

Hello, 

  I frequently receive multi-punch data "exploded" into separate columns, where a positive response for option 1 = 1, option 2 = 2, etc.. I have to recode these to binary and I'd like to have this in a reproducable script. I think I'm on the right track with the following script, but I get the error "object not subscriptable{1} in access or evaluation of 'Subscript'" when I run it. I've attached a sample data set. Thanks in advance.

 

 

dt = Current Data Table();

lstColNames = {"14_1","14_2","14_3","14_4","14_5","14_6","14_7","14_8","14_9","14_10","14_11","14_12","14_13"};
For Each Row( For( i = 1, i <= N Items( lstColNames ), i++, If( lstColNames[i][]== i, lstColNames[i][] = 1 ) ) );

 

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Recode a series of columns with a loop

See if this is what you want

Names Default To Here( 1 );
dt = Current Data Table();

lstColNames = dt << get column names( string, nominal );
For( i = 1, i <= N Items( lstColNames ), i++,
	theRows = dt << get rows where( As Column( dt, lstColNames[i] ) >= 1 );
	If( N Rows( theRows ) > 0,
		Column( dt, lstColNames[i] )[theRows] = 1
	);
	theRows = dt << get rows where( Is Missing( As Column( dt, lstColNames[i] ) ) == 1 );
	If( N Rows( theRows ) > 0,
		Column( dt, lstColNames[i] )[theRows] = 0
	);
	Column( dt, lstColNames[i] ) << delete property( "Value Labels" );
Jim
4 REPLIES 4
txnelson
Super User

Re: Recode a series of columns with a loop

See if this is what you want

Names Default To Here( 1 );
dt = Current Data Table();

lstColNames = dt << get column names( string, nominal );
For( i = 1, i <= N Items( lstColNames ), i++,
	theRows = dt << get rows where( As Column( dt, lstColNames[i] ) >= 1 );
	If( N Rows( theRows ) > 0,
		Column( dt, lstColNames[i] )[theRows] = 1
	);
	theRows = dt << get rows where( Is Missing( As Column( dt, lstColNames[i] ) ) == 1 );
	If( N Rows( theRows ) > 0,
		Column( dt, lstColNames[i] )[theRows] = 0
	);
	Column( dt, lstColNames[i] ) << delete property( "Value Labels" );
Jim
lilysecret
Community Trekker

Re: Recode a series of columns with a loop

Thanks, TX, that works for the sample I posted perfectly. How would I restrict this to only columns 14_1 to 14_13 when they are in a larger dataset that would include many more columns? That's why I was trying to use a specific list.

0 Kudos
Highlighted
txnelson
Super User

Re: Recode a series of columns with a loop

For( i = 1, i <= 13, i++,

Just reduce the For() loop

Jim
lilysecret
Community Trekker

Re: Recode a series of columns with a loop

Thanks! I was able to use this to create a reproducable script for my projects. I appreciate the assist!

0 Kudos