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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

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
  • 2 replies
  • 1843 views
  • 0 likes
  • 3 in conversation