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 🙂
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;
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.
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
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;
Glad you understand what was needed
Hi Kurt,
Thank you very much this worked brilliantly that is the exact order i was looking for!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.