BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Cydney
Calcite | Level 5

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 🙂 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Cydney
Calcite | Level 5

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

 

 

Kurt_Bremser
Super User

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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Glad you understand what was needed Smiley Frustrated

Cydney
Calcite | Level 5

Hi Kurt,

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

novinosrin
Tourmaline | Level 20

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 775 views
  • 1 like
  • 4 in conversation