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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: