BookmarkSubscribeRSS Feed
whiteco
Calcite | Level 5

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? 

6 REPLIES 6
novinosrin
Tourmaline | Level 20

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

PaigeMiller
Diamond | Level 26

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
Reeza
Super User

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/

Astounding
PROC Star

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.

 

 

ChrisNZ
Tourmaline | Level 20

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,

ballardw
Super User

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

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!

How to Concatenate Values

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.

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
  • 6 replies
  • 608 views
  • 7 likes
  • 7 in conversation