topic Re: Reordering a variables in a data set with 150 columns in SAS Programming

Reordering a variables in a data set with 150 columns

whiteco — Tue, 31 Jul 2018 00:07:08 GMT

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?

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

novinosrin — Tue, 31 Jul 2018 00:11:50 GMT

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

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

PaigeMiller — Tue, 31 Jul 2018 00:50:05 GMT

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.

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

Reeza — Tue, 31 Jul 2018 01:07:50 GMT

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/

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

Astounding — Tue, 31 Jul 2018 01:22:05 GMT

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.

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

ChrisNZ — Tue, 31 Jul 2018 02:56:39 GMT

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,

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

ballardw — Tue, 31 Jul 2018 14:52:26 GMT

@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.