topic Reducing columns in a transposed dataset in SAS Programming

Reducing columns in a transposed dataset

Doug____ — Thu, 05 Sep 2019 19:34:58 GMT

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

Re: Reducing columns in a transposed dataset

data_null__ — Thu, 05 Sep 2019 20:19:01 GMT

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;

Re: Reducing columns in a transposed dataset

Ksharp — Fri, 06 Sep 2019 12:44:10 GMT

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;