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
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.