topic Re: change rows order in SAS Programming

change rows order

echoli — Mon, 13 Mar 2017 17:24:20 GMT

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

Re: change rows order

art297 — Mon, 13 Mar 2017 17:33:57 GMT

proc sort data=yourdatasetname;
  by yourcolumnname;
run;

Art, CEO, AnalystFinder.com

Re: change rows order

echoli — Mon, 13 Mar 2017 17:36:58 GMT

Hi ART297,

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

Thanks,

Chen

Re: change rows order

art297 — Mon, 13 Mar 2017 17:40:49 GMT

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

Re: change rows order

rogerjdeangelis — Mon, 13 Mar 2017 17:42:25 GMT

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;

Re: change rows order

collinelliot — Mon, 13 Mar 2017 18:26:57 GMT

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;

Re: change rows order

abhishekdixit93 — Fri, 10 Jan 2020 06:10:26 GMT

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!

Re: change rows order

art297 — Fri, 10 Jan 2020 15:30:03 GMT

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

Re: change rows order

DrAbhijeetSafai — Thu, 07 Oct 2021 04:58:16 GMT

Thanks a lot. It was very useful.