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

[corella]

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

[twocanbazza · Accepted solution]

What about

by ID Date;

if First.Date or last.Date then output;

Cheers

Barry

[corella]

Thanks! It was so simple

[sbb]

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.

Suggested Google advanced search argument, this topic / post:

by group processing site:sas.com

[corella]

Thank you Barry!

[art297]

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

[corella]

It worked! Thank you!

[vraj1]

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;

[Henary]

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??

[twocanbazza]

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

[Raju2202]

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;

Thanks in advance!