Help using Base SAS procedures

Collapsing multiple columns into few

Accepted Solution Solved
Reply
Contributor
Posts: 63
Accepted Solution

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

View solution in original post


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,256

Re: Collapsing multiple columns into few

Does not the original column name or order have any meaning? How will you use the table further on?
I would sugest that you transpose the table instead, resulting in record, class_no and class_value columns. See PROC TRANSPOSE for details.
Data never sleeps
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 304 views
  • 1 like
  • 3 in conversation