topic first.[variable], last.[variable] question in SAS Procedures

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

corella — Thu, 07 Jul 2011 20:57:56 GMT

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

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

twocanbazza — Thu, 07 Jul 2011 21:52:53 GMT

What about

by ID Date;

if First.Date or last.Date then output;

Cheers

Barry

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

sbb — Thu, 07 Jul 2011 21:56:25 GMT

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

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

art297 — Thu, 07 Jul 2011 21:56:54 GMT

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

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

corella — Fri, 08 Jul 2011 13:46:10 GMT

Thanks! It was so simple

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

corella — Fri, 08 Jul 2011 13:46:45 GMT

Thank you Barry!

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

corella — Fri, 08 Jul 2011 13:47:18 GMT

It worked! Thank you!

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

Henary — Wed, 07 Mar 2012 02:15:28 GMT

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

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

twocanbazza — Wed, 07 Mar 2012 02:23:08 GMT

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

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

Raju2202 — Thu, 15 Dec 2016 20:36:13 GMT

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!

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

vraj1 — Wed, 30 Aug 2017 13:09:36 GMT

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;