BookmarkSubscribeRSS Feed
Doug____
Pyrite | Level 9

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

2 REPLIES 2
data_null__
Jade | Level 19

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
Super User

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 390 views
  • 0 likes
  • 3 in conversation