Solved
Contributor
Posts: 63

# Collapsing multiple columns into few

I want to collapse multiple columsn (as some are empty) into few columsn to reduce size of the file.

Maximum columns populated in Have is 3 so poutput should have 3 columns and orignial 5 can be dropped

Have

Record             Class1               Class2             Class3           Class4          Class5

1                        A                                               C

2                                                   B

3                                                                                                D                    E

4                       A

5                                                      B                  C                   D

Want

Record        Classnew1               ClassNew2             ClassNew3

1                     A                                 C

2                     B

3                     D                                 E

4                     A

5                     B                                 C                            D

Accepted Solutions
Solution
‎10-13-2015 02:06 PM
Valued Guide
Posts: 765

## Re: Collapsing multiple columns into few

[ Edited ]

Hi, I'm sure this question will get lots of replies.  Here's on idea ...

data x;
input record (class1-class5) (:\$1.);
datalines;
1 A . C . .
2 . B . . .
3 . . . D E
4 A . . . .
5 . B C D .
;

data y (keep=record class: );
set x;
array class(5);
new = catt(of class(*));
do i=1 to 5;
class(i) = char(new,i);
end;
run;

data set Y ...

Obs    record    class1    class2    class3    class4    class5

1        1        A         C
2        2        B
3        3        D         E
4        4        A
5        5        B         C         D

All Replies
Solution
‎10-13-2015 02:06 PM
Valued Guide
Posts: 765

## Re: Collapsing multiple columns into few

[ Edited ]

Hi, I'm sure this question will get lots of replies.  Here's on idea ...

data x;
input record (class1-class5) (:\$1.);
datalines;
1 A . C . .
2 . B . . .
3 . . . D E
4 A . . . .
5 . B C D .
;

data y (keep=record class: );
set x;
array class(5);
new = catt(of class(*));
do i=1 to 5;
class(i) = char(new,i);
end;
run;

data set Y ...

Obs    record    class1    class2    class3    class4    class5

1        1        A         C
2        2        B
3        3        D         E
4        4        A
5        5        B         C         D

Super User
Posts: 5,884