topic Reordering data by date, but grouping ids in SAS Procedures

Reordering data by date, but grouping ids

Cydney — Wed, 30 Jan 2019 09:36:26 GMT

Hi,

I have a data set as;

Id Date

1 2005

2 2008

3 2004

3 2012

4 2001

5 2016

5 2007

5 2010

I want to order the data so that it is sorted by date order, but keep the same id's together, so it would look like this;

Id Date

4 2001

3 2004

3 2012

1 2005

5 2007

5 2010

5 2016

2 2008

The only way i have found to do his so far is using proc sort, but this seems to either order by date which muddles the ids, or I can sort by dates within id but this doesn't reorder the id's so that the earliest is first.

Any help is really appreciated, thanks in advance 🙂

Re: Reordering data by date, but grouping ids

RW9 — Wed, 30 Jan 2019 10:01:05 GMT

Well, probably the reason why you can't get that sort, and why i can see no order, is because there doesn't appear to be any logical ordering. Please can you explain exactly - giving the specific examples therin, what the order here is? From what I can see ID is not in order, nor is date, therefore the data is not ordered. You say grouping by ids, but again the data is not grouped by ids, its not grouped by anything.

Re: Reordering data by date, but grouping ids

Cydney — Wed, 30 Jan 2019 10:39:28 GMT

Yes sorry, this list will be used to chase up data so needs to be in date order to make the task easier, but whilst looking up a specific id with multiple records it would make more sense to check them all in one go.

If you look at just the first instance of each id they are all in date order i.e

Id Date

4 2001

3 2004

1 2005

5 2007

2 2008

But rather than have the extra records for each case in date order i want to keep them with the first record

Re: Reordering data by date, but grouping ids

Kurt_Bremser — Wed, 30 Jan 2019 11:28:05 GMT

Create a format for the chronological order, and then use the resulting variable:

data have;
input id date;
cards;
1 2005
2 2008
3 2004
3 2012
4 2001
5 2016
5 2007
5 2010
;
run;

/* create a format that holds a sequential number for id's, based on the first year for each */
proc sort
  data=have
  out=cntlin (rename=(id=start))
;
by id date;
run;

data cntlin;
set cntlin;
by start;
if first.start;
run;

proc sort data=cntlin;
by date;
run;

data cntlin;
set cntlin;
label = _n_;
fmtname = 'myfmt';
type = 'N';
run;

proc format cntlin=cntlin;
run;

/* use that format */
data want;
set have;
number = put(id,myfmt.);
run;

/* create final order */
proc sort data=want;
by number date;
run;

Re: Reordering data by date, but grouping ids

RW9 — Wed, 30 Jan 2019 11:30:10 GMT

Glad you understand what was needed

Re: Reordering data by date, but grouping ids

Cydney — Wed, 30 Jan 2019 11:48:45 GMT

Hi Kurt,

Thank you very much this worked brilliantly that is the exact order i was looking for!

Re: Reordering data by date, but grouping ids

novinosrin — Wed, 30 Jan 2019 16:12:57 GMT


data have;
input id date;
cards;
1 2005
2 2008
3 2004
3 2012
4 2001
5 2016
5 2007
5 2010
;
run;


proc sort data=have out=_h;
by date id;
run;

data t;
if _n_=1 then do;
  declare hash H (multidata:'y',ordered:'y') ;
   h.definekey  ("id") ;
   h.definedata ("seq") ;
    h.definedone () ;
end;
set _h end=l;
if h.find() ne 0 then seq+1;
h.replace();
run;

proc sort data=t out=want(drop=seq);
by seq date;
run;