## first.[variable], last.[variable] question

Solved
Occasional Contributor
Posts: 11

# first.[variable], last.[variable] question

Hello,

I am extracting data from a table like this one below and having difficulties with first.[variable] , last.[variable] processing. I need to get first and last observations for each ID&Date together. For instance, for ID=100 I should receive obs 1, 3 as 'first' and obs 2, 6 as 'last'. For ID=101 it should be obs 7, 9 as 'first' and 8, 10 as 'last'. Instead my program reads only the ID variable and returns obs 1 as 'first' and obs 6 as 'last' for ID=100, then obs 7 as 'first' and obs 10 as 'last' for ID=101. Example of my code for 'last':

data get_last;

set master_table;

by ID Date;

if last.ID and last.Date then output;

run;

What am I doing wrong? How to get my first and last observations based on two criteria together?  Thank you.

 obs ID Date Transaction Code Amount 1 100 14-Jul-09 1 345 2 100 14-Jul-09 2 234 3 100 16-Jul-09 1 76 4 100 16-Jul-09 2 234 5 100 16-Jul-09 2 278 6 100 16-Jul-09 2 45 7 101 20-Jul-09 1 7 8 101 20-Jul-09 2 896 9 101 25-Jul-09 1 45 10 101 25-Jul-09 2 23

Accepted Solutions
Solution
‎05-03-2017 08:52 AM
Super Contributor
Posts: 356

## first.[variable], last.[variable] question

by ID Date;

if First.Date or last.Date then output;

Cheers

Barry

All Replies
Solution
‎05-03-2017 08:52 AM
Super Contributor
Posts: 356

## first.[variable], last.[variable] question

by ID Date;

if First.Date or last.Date then output;

Cheers

Barry

Occasional Contributor
Posts: 11

## first.[variable], last.[variable] question

Thanks! It was so simple

Super Contributor
Posts: 3,176

## first.[variable], last.[variable] question

Some self-diagnosis output can be generated by adding this statement to a DATA step, so you can see in your SASLOG the SAS-generated temporary variables for FIRST.<byvar_> and/or LAST.<byvar_>.  This information will help diagnose the program's execution more visually.

Basically, you are only going to "OUTPUT" the last-occurrence condition of both of your BY statement variables.  So, then, if you have a requirement to generate observations for other "OR" conditions, you will need to code them in your IF (<expression1>) OR (<expression2) THEN <action>;   statement.

Scott Barry
SBBWorks, Inc.

by group processing site:sas.com

Occasional Contributor
Posts: 11

Thank you Barry!

PROC Star
Posts: 8,169

## first.[variable], last.[variable] question

Of course your data have to be sorted by id and date.

If it is, then your datastep is simply trying for the wrong things. As I understand what you want, you simply want the first and last records for a given ID on any date.

If so, then you only need:

data get_first_and_last;

set master_table;

by ID Date;

if first.date or last.Date then output;

run;

Of course, if you only want the last record on a date then replace the if with:

if last.Date then output;

HTH,

Art

Occasional Contributor
Posts: 11

## first.[variable], last.[variable] question

It worked! Thank you!

Regular Contributor
Posts: 214

## Re: first.[variable], last.[variable] question

I tried using  the same code but it didnt work. i need to get for each usubjid i need the first ECSTDTC(start date) and last ECENDTC (end date) so basically one record per usubjid.

proc sort data=ec out=ec7; by usubjid ECSTDTC; run;
data ec1;
set ec7;
by usubjid ECSTDTC ECENDTC;
if First.ECSTDTC and last.ECENDTC then output;
run;

Occasional Contributor
Posts: 8

## first.[variable], last.[variable] question

Hi can anyone explain me that what is logic behind this sentence in SAS.

"IF NOT FIRST.CPNP AND LAST.CPNP AND PLANT='USM' THEN DELETE;"

I am new to SAS so, please let me know that this query will delete duplicate rows from the data set or something else??

Super Contributor
Posts: 356

## first.[variable], last.[variable] question

This will delete the last CPNP record if it is not the first record of the CPNP group and where plant=USM only.

It will not delete all duplicates if your CPNP group has more than two records.

Barry

Occasional Learner
Posts: 1

## Re: first.[variable], last.[variable] question

Guys,

I would like to get the maximum date value from VCI_setup-date

but in the if condition I want to give all the three variables name, group , chart type

All these three variables by together have different dates and I want only the maximum.

data name_contry1;

set name_country;

by name group chart_type descending VCI_SETUP_Date;

if first.name;

run;