Method for Unsorted data

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: 10,265

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
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 9,599

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;


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
  • 3 in conversation