topic Re: Simple task - remove empty columns in Discussions

Simple task - remove empty columns

viralnerd2013 — Tue, 09 Apr 2013 19:51:24 GMT

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

Patient	Sample	AA_2	AA_4	AA_5	AA_6
2234	Day 1	A			C
2234	Day 11	A	V/M/S		C
2234	Day 21	A			C
2234	Day 24	A		S/T	C

Output

Patient	Sample	AA_2	AA_4	AA_5	AA_6
2234	Day 1	A			C
2234	Day 11	A	V/M/S		C
2234	Day 21	A			C
2234	Day 24	A		S/T	C

Re: Simple task - remove empty columns

ms — Tue, 09 Apr 2013 20:30:23 GMT

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 );

Re: Simple task - remove empty columns

viralnerd2013 — Tue, 09 Apr 2013 21:04:24 GMT

Works beautifully! Thank you very much.

Re: Simple task - remove empty columns

gbu — Wed, 10 Apr 2013 08:25:21 GMT

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);

Re: Simple task - remove empty columns

viralnerd2013 — Wed, 10 Apr 2013 13:24:27 GMT

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

Re: Simple task - remove empty columns

gbu — Thu, 25 Apr 2013 14:18:53 GMT

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

Print(lstDelete);