Solved
Contributor
Posts: 37

# consolidating columns

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

Major

student

class1class2class3class4class location
ABJackenglishmathcampus1

AB

KenartPEmathenglishcampus2
ABKenhistorycampus1
CDMikeenglishhistorycampus3

CD

Nancyartcampus2

Want to transform the above table into this:

Majorstudentclassclass location
ABJackenglish

campus1

ABJackmath

campus1

ABKenartcampus2
AB

Ken

PEcampus2
AB

Ken

mathcampus2
AB

Ken

englishcampus2
ABKenhistorycampus1
CDMikeenglish

campus3

CDMikehistory

campus3

CDNancyartcampus2

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

Accepted Solutions
Solution
‎05-14-2012 02:29 PM
Super User
Posts: 6,762

## Re: consolidating columns

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.

All Replies
Super Contributor
Posts: 1,636

## Re: consolidating columns

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

Contributor
Posts: 37

## Re: consolidating columns

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;

Solution
‎05-14-2012 02:29 PM
Super User
Posts: 6,762

## Re: consolidating columns

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.

Contributor
Posts: 37

yeah!  it works.

Contributor
Posts: 37

## Re: consolidating columns

thanks to Linlin and

🔒 This topic is solved and locked.