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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.