BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Cyndia
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

View solution in original post

5 REPLIES 5
Linlin
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
PROC Star

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
Calcite | Level 5

yeah!  it works.Smiley Wink

Cyndia
Calcite | Level 5

thanks to Linlin and 

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 961 views
  • 3 likes
  • 3 in conversation