BookmarkSubscribeRSS Feed
echoli
Obsidian | Level 7

Hi All,

 

I met a question here:

I have a dataset with 32 rows (not columns), and I want to change the order of rows by what I want. for example, if I have the dataset with order:

gender

age

lab1

lab2

But I want the row order like:

age

lab1

lab2

gender

any way can solve this?

 

Thanks,

Chen

8 REPLIES 8
art297
Opal | Level 21

 

proc sort data=yourdatasetname;
  by yourcolumnname;
run;

Art, CEO, AnalystFinder.com

 

echoli
Obsidian | Level 7

Hi ART297,

 

I don't want to change my columns order, I want to change rows order. Any idea?

 

Thanks,

Chen

art297
Opal | Level 21

You said that those values were all in the same column, but on different rows. If it's a SAS dataset, the column has to have a name.

 

If that doesn't answer your question, post an example of your file as a SAS data step.

 

Art, CEO, AnalystFinder.com

DrAbhijeetSafai
Pyrite | Level 9
Thanks a lot. It was very useful.
Dr. Abhijeet Safai
Associate Data Analyst
Actu-Real
rogerjdeangelis
Barite | Level 11
HAVE
====

Up to 40 obs WORK.HAVE total obs=4

Obs    GRP

 1     gender
 2     age
 3     lab1
 4     lab2

WANT
====

Up to 40 obs WORK.WANT total obs=4

Obs    GRP

 1     age
 2     lab1
 3     lab2
 4     gender

FULL SOLUTION
=============

* create some data;

data have;
input grp $;
cards4;
gender
age
lab1
lab2
;;;;
run;quit;

proc format;
  value $grp2odr
  'age'    = '01'
  'lab1'   = '02'
  'lab2'   = '03'
  'gender' = '04'
;run;quit;

proc sql;
  create
    table want as
  select
    *
  from
    have
  order
    by put(grp,$grp2odr.)
;quit;


collinelliot
Barite | Level 11

I think the better approach is with informat and formats, but just strictly with respect to the question of whether you can get what you want with the data you have, you can do an order by in proc sql where you sort on a boolean value first to force the order you want:

 

 

data have;
input var $;
datalines;
gender
age
lab1
lab2
;

proc sql;
    CREATE TABLE want AS
    SELECT *
    FROM have
    ORDER BY var eq 'gender', var;
quit; 
abhishekdixit93
Calcite | Level 5
Hi, That's also a good approach. But what if we have 1000 observations. We can't give 01,02,03...and so on for all variables. Any approach to reorder such a large no. of observations.

Thanks!
art297
Opal | Level 21

This thread is from 3 years ago. I don't know if you were the original poster, but neither you nor they (if it wasn't you) ever provided an example of what your actual data looked like.

 

If you have gender, age, lab1 and lab2 records (in that order) for each subject, and you simply want to move the gender record to be in the fourth position within each subject's collection of records, then the following should work:

/* this first datastep is simply to create an example */
/* of what your data might currently look like */
data have;
  input var $ value $;
  datalines;
gender M
age 24
lab1 78
lab2 87
gender F
age 30
lab1 65
lab2 56
gender M
age 32
lab1 84
lab2 48
;
run;

data need;
  set have;
  if var eq "gender" then do;
    id+1;
    order=1;
  end;
  else order=0;
run;

proc sort data=need out=want (drop=order);
  by id order;
run;

Art, CEO, AnalystFinder.com

 

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
  • 8 replies
  • 9626 views
  • 4 likes
  • 6 in conversation