topic Collapsing multiple columns into few in SAS Procedures

Collapsing multiple columns into few

buckeyefisher — Sat, 10 Oct 2015 13:47:02 GMT

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

Re: Collapsing multiple columns into few

MikeZdeb — Sat, 10 Oct 2015 16:26:49 GMT

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

Re: Collapsing multiple columns into few

LinusH — Sat, 10 Oct 2015 21:16:30 GMT

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.