consolidating columns

[Cyndia]

I have a table like this, but would like to consolidate all the class1, class2, class3, class4 columns into one column only:

Major	student	class1	class2	class3	class4	class location
AB	Jack	english	math			campus1
AB	Ken	art	PE	math	english	campus2
AB	Ken	history				campus1
CD	Mike	english	history			campus3
CD	Nancy	art				campus2

Want to transform the above table into this:

Major	student	class	class location
AB	Jack	english	campus1
AB	Jack	math	campus1
AB	Ken	art	campus2
AB	Ken	PE	campus2
AB	Ken	math	campus2
AB	Ken	english	campus2
AB	Ken	history	campus1
CD	Mike	english	campus3
CD	Mike	history	campus3
CD	Nancy	art	campus2

what is the best way to do that, and save the new table as a sas table only?  Thanks!

[Linlin]

try:

data want;

length class $8;

set have;

array _c(*) class:;

do _n_=1 to dim(_c) while (_c(_n_) ne ' ');

class=_c(_n_);

output;

drop class1 class2 class3 class4;

run;

Below is the modified version:smileysilly:

data want;

length class $8;

set have;

array _c(*) class1-class4;

do _n_=1 to dim(_c) while (_c(_n_) ne ' ');

class=_c(_n_);

output;

end;

drop class1-class4;

run;

Message was edited by: Linlin

[Cyndia]

I tried the following, the result table has 4 columns, class, major, student, and class location, but all the classes are removed, class column became empty...why?

data want;

length class $10.;

set have;

array _c(*) class:;

do _n_=1 to dim(_c) while (_c(_n_) ne ' ');

class=_c(_n_);

end;

output;

drop class1 class2 class3 class4;

run;

[Astounding · Accepted solution]

There are two problems.  First, the array contains CLASS as well as CLASS1-CLASS4.  Change CLASS: to spell out the list:  CLASS1-CLASS4.

The second problem is that the OUTPUT statement needs to go before the END statement.

Double-check the results, and can be tweaked if necessary.

Good luck.

[Cyndia]

yeah!  it works.

[Cyndia]

thanks to Linlin and  Astounding