topic consolidating columns in SAS Procedures

consolidating columns

Cyndia — Mon, 14 May 2012 16:30:55 GMT

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!

Re: consolidating columns

Linlin — Mon, 14 May 2012 17:01:47 GMT

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

Re: consolidating columns

Cyndia — Mon, 14 May 2012 18:20:55 GMT

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;

Re: consolidating columns

Astounding — Mon, 14 May 2012 18:29:24 GMT

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.

Re: consolidating columns

Cyndia — Mon, 14 May 2012 18:33:17 GMT

yeah! it works.

Re: consolidating columns

Cyndia — Mon, 14 May 2012 18:34:48 GMT

thanks to Linlin and Astounding