BookmarkSubscribeRSS Feed
jadhavsada1_gmail_com
Fluorite | Level 6

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

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

CFC_SAS_Communities_400x225.jpg

Call for content now open!

It's your turn to help shape SAS Innovate 2027. Share your expertise and inspire the SAS community.

Submit your proposal →

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 3740 views
  • 0 likes
  • 3 in conversation