change rows order

[echoli]

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

[art297]

proc sort data=yourdatasetname;
  by yourcolumnname;
run;

Art, CEO, AnalystFinder.com

[echoli]

Hi ART297,

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

Thanks,

Chen

[art297]

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]

Thanks a lot. It was very useful.

[rogerjdeangelis]

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]

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]

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]

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