369194_en-US

liliconcom

Transposing from WIDE to LONG and a formula question

Hi--two somewhat related questions

I have used SPLIT to transform a long file to a wide file, but I have a slightly more complicated situation where I want to STACK/transpose a wide file to a long file.

 

I have a dataset with a unique subject ID, and then several hundred columns/variables. The variable names are the exact same with the exception that Year 1 ends '2', Year 2 ends in '3', etc. Is there a relatively simple way to make this file long, such that there is a time variable (1,2,3,4) and the variable names are only in there once with no number at the end?

 

Related but approaching the above in a different way, in such a dataset, is there a way to create a formula in JMP to tell it for all XXX hundred columns, to remove the # (e.g., 1, 2) from the end of the column/variable name? Obviously I could do that manually but it would take forever.

 

Thanks so much!

Data TableOtherScriptingRe: Transposing from WIDE to LONG and a formula question

Here is a script that will generate a stacked data table with 3 columns

Here is a script that will strip off the numerical number from the end of columns in a data table.  Remember, when renaming columns, if the end result is a column name that is the same as a previous column's name, JMP will add a numeric value to make the new column name a unique name

Names Default To Here( 1 );
dt = Current Data Table();
For( i = 2, i <= N Cols( dt ), i++,
 if(
  is missing(
   Num(
    word(
     -1,
     Lowercase( Column( dt, i ) << get name )
    ,
    "abcdefghijklmnopqrstuvwxyz;:, -_)(")
   )
  ) == 0,
  Column( dt, i ) <<
  set name(
   substr(
    Column(dt,i)<<GET name=""></GET>

Names Default To Here( 1 );
dt = new table( "Example",
 add rows( 10 ),
 New Column( "Year2", formula( Random Integer( 1, 100 ) ) ),
 New Column( "Year3", formula( Random Integer( 1, 100 ) ) ),
 New Column( "Year4", formula( Random Integer( 1, 100 ) ) )
);

dtStack = Data Table( "Example" ) << Stack(
 columns( :Year2, :Year3, :Year4 ),
 Source Label Column( Column ),
 Stacked Data Column( "Data" )
);

dtStack << New Column( "Year",
 formula(
  Num(
   Word(
    -1,
    Lowercase( :Column ),
    "abcdefghijklmnopqrstuvwxyz;:, -_)("
   )
  )
 )
);
dtStack:Year << delete formula;
for each row(
 :Column = substr(
  :Column,
  1,
  Length( :Column ) - Length(
   Word(
    -1,
    Lowercase( :Column ),
    "abcdefghijklmnopqrstuvwxyz;:, -_)("
   )
  )
 )
);

Re: Transposing from WIDE to LONG and a formula question

I really appreciate your reply.  If memory serves you also helped me out with a question last year.

 

Unfortunately I am not savvy with JMP script, only point and click.  And even if I could figure it out, since I have a lot of variables, I would have to run that code for each wouldn't I?