Reducing columns in a transposed dataset

[Doug____]

I have a dataset with probably 150 columns transposed. Now for each record, most values are missing. How can I collapse the columns for each record so only the cells with values are left for each record (these are character values).

For example:

Record                          col1                        col2                   col3                     col4

1                                                                   X                         Y                       Z

2                                     X                                                       Z

The result I want should look something like this:

Record                       newcol1                           newcol2               newcol3

1                                      X                                     Y                        Z

2                                      X                                     Z

[data_null__]

Seems like a WHERE statement to filter the missing values from the DATA= data set would work to produce the desired output directly from PROC TRANSPOSE.

proc transpose data=
   where not missing(transpose-variable);
   run;

[Ksharp]

If you do not have a big table ,try PROC TRANSPOSE .

data have;
input Record                 (         col1                        col2                   col3                     col4) ($);
cards;
1                                      .                             X                         Y                       Z
2                                     X                              .                         Z  .
;
proc transpose data=have out=temp(drop=_: where=(col1 is not missing));
by record;
var col:;
run;
proc transpose data=temp out=want(drop=_:);
by record;
var col1;
run;