Reordering a variables in a data set with 150 columns

Reply
Frequent Learner
Posts: 1

Reordering a variables in a data set with 150 columns

I have a data set with the following 151 columns: 

 

StudentID

 

Exam1

Exam2

Exam3

.

.

Exam50

 

Date1

Date2

Date3

.

.

Date50

 

Score1

Score2

Score3

.

.

.

Score50

 

Question: How do I reorder this large set of variables as follows - 

 

StudentID

Exam1

Date1

Score1

 

Exam2

Date2

Score2

 

.

.

.

Exam50

Date50

Score50?

 

I am at the level of a beginner. Is there a way I can reorder using RETAIN, but without manually entering the variables in the RETAIN statement? 

Super User
Posts: 2,068

Re: Reordering a variables in a data set with 150 columns

you can query sashelp.vcolumn or dicitonary.columns

extract the number from each into a new var

sort according to new var numbers

load into a macro var separated by ' '

call the macro var in retain 

 

wish i could demonstrate , but feeling too lazy and tired. My apologies

Respected Advisor
Posts: 3,288

Re: Reordering a variables in a data set with 150 columns

There's only a very few reasons to do this, most of the time you are wasting your time. So don't do this. If you need things in a specific order for output reasons, PROC PRINT, PROC REPORT, PROC TABULATE all allow you to specify the order the variables appear in your output, regardless of the order of the variables in the data set. REPEATING: don't do this.

--
Paige Miller
Super User
Posts: 24,026

Re: Reordering a variables in a data set with 150 columns

It may be easier to transpose your data, via PROC TRANSPOSE/Data step, add a new variable with an order and then retranspose. Though I suspect you may find a long form to work with. 

 

Heres a tutorial on going wide to long. 

https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/

Super User
Posts: 6,935

Re: Reordering a variables in a data set with 150 columns

You don't have to enter the variable names manually into a RETAIN statement.  A reasonably straightforward macro can do that for you.  For example:

 

%macro varlist;

   %local k;

   %do k=1 %to 50;

      Exam&k  Date&k  Score&k

   %end;

%mend varlist;

 

data want;

   retain StudentID %varlist;

   set have;

run;

 

You end up with the same list of variables in the RETAIN statement, but the computer does the work of generating the list.

 

 

Super User
Posts: 2,516

Re: Reordering a variables in a data set with 150 columns

See @Astounding's reply for an answer.

Unless you have a very good reason, you'd be much better off having just 4 columns: ID, EXAM, DATE, SCORE

A long table is much much easier to work with than a wide one,

Super User
Posts: 13,942

Re: Reordering a variables in a data set with 150 columns


@ChrisNZ wrote:

See @Astounding's reply for an answer.

Unless you have a very good reason, you'd be much better off having just 4 columns: ID, EXAM, DATE, SCORE

A long table is much much easier to work with than a wide one,


And possibly an ORDER, or similarly named, variable to keep track of exam1, exam2 etc.

Ask a Question
Discussion stats
  • 6 replies
  • 162 views
  • 7 likes
  • 7 in conversation