Method for Unsorted data

Reply
Occasional Contributor
Posts: 15

Method for Unsorted data

What is a method for assigning first.VAR and last.VAR to the BY group variable on unsorted data?

Super User
Posts: 7,766

Re: Method for Unsorted data

Posted in reply to jadhavsada1_gmail_com

Use the notsorted option, but be aware that this can cause false results.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,942

Re: Method for Unsorted data

Posted in reply to jadhavsada1_gmail_com

There isn't one in that sense.  first/last means a record which appears first or last in a sorted group - that being said if you know your groupings and what order would highlight first or last then you could programmatically do it.  I.e. if I have a set of data by id, with a date, then I could assume that date sequential would be the order and do:

proc sql;

     create table WANT as

     select     A.*,

                   case     when A.DATE=B.MIN_DATE then 1

                               else 0 end as FIRST,

                   case     when A.DATE=B.MAX_DATE then 1

                               else 0 end as LAST

     from       HAVE A

     left join   (select     distinct ID,

                                   min(DATE) as MIN_DATE,

                                   max(DATE) as MAX_DATE

                     from       HAVE

                     group by ID) B

     on           A.ID=B.ID;

quit;

However, why?  Is order of your data that important?  If so create a temporary variable called sort, and assign it to _n_.  Sort your data and get first/last, then sort your data by the temporary variable setting it back to old sort.  Again, why bother though.

Ask a Question
Discussion stats
  • 2 replies
  • 790 views
  • 0 likes
  • 3 in conversation