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!
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.
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
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;
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.
yeah! it works.
thanks to Linlin and Astounding
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.