cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
viralnerd2013
Level II

Simple task - remove empty columns

Hello SAS forum,

I am trying to remove all columns in a dataset that are empty, and I cannot figure out how to do this in JMP9. Can you help? There will be variable number of patients and AA's

Data input

   

PatientSample
AA_1AA_2AA_3AA_4AA_5AA_6
2234Day 1AC
2234Day 11AV/M/SC
2234Day 21AC
2234Day 24AS/TC

Output

PatientSample
AA_2AA_4AA_5AA_6
2234Day 1AC
2234Day 11AV/M/SC
2234Day 21AC
2234Day 24AS/TC


1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Simple task - remove empty columns

When doing this interactively in JMP, I tend to use missing data pattern and summary. But for large tables and repeated tasks JSL script is more effective.

This should work for removing empty columns of any data type.

dt = Current Data Table();

nc = N Col( dt );

names = dt << Get Column Names();

dt1 = dt << Missing Data Pattern( columns( Eval( names ) ), invisible );

nr = N Row( dt1 );

For( i = nc, i >= 1, i--,

  If( Col Sum( Column( dt1, i + 3 ) ) < nr,

  Remove From( names, i )

  )

);

Try( dt << delete columns( names ) );

Close( dt1, nosave );

View solution in original post

5 REPLIES 5
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Simple task - remove empty columns

When doing this interactively in JMP, I tend to use missing data pattern and summary. But for large tables and repeated tasks JSL script is more effective.

This should work for removing empty columns of any data type.

dt = Current Data Table();

nc = N Col( dt );

names = dt << Get Column Names();

dt1 = dt << Missing Data Pattern( columns( Eval( names ) ), invisible );

nr = N Row( dt1 );

For( i = nc, i >= 1, i--,

  If( Col Sum( Column( dt1, i + 3 ) ) < nr,

  Remove From( names, i )

  )

);

Try( dt << delete columns( names ) );

Close( dt1, nosave );

viralnerd2013
Level II

Re: Simple task - remove empty columns

Works beautifully! Thank you very much.

gbu
gbu
Level III

Re: Simple task - remove empty columns

Hello,

If you want to keep the empty column name into list, you could also process like this.

dt = Current Data Table();

iColMax = NCol(dt);

iRowMax = NRow(dt);

null = 0;

lstDelete = List();

For (iCol = 1, iCol<=iColMax, iCol++,

    null = 0;

    col = Column(iCol);

    strColName = Column(iCol) << GetName;

For (iRow = 1, iRow<=iRowMax, iRow++,

  If (Or(IsEmpty(col[iRow]),IsMissing(col[iRow])),

   null = null + 1;

  );

);

If (null == iRowMax, InsertInto(lstDelete, Eval(strColName))

);

);

dt << Delete Columns(lstDelete);

viralnerd2013
Level II

Re: Simple task - remove empty columns

Thank you. I ran this but did not see the list, am I missing something?

gbu
gbu
Level III

Re: Simple task - remove empty columns

If you wanna see the list, you can just use :

Print(lstDelete);