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?
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
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.
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/
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.
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,
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.