Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

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.

obsIDDateTransaction CodeAmount
110014-Jul-091345
210014-Jul-092234
310016-Jul-09176
410016-Jul-092234
510016-Jul-092278
610016-Jul-09245
710120-Jul-0917
810120-Jul-092896
910125-Jul-09145
1010125-Jul-09223

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

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

What about

by ID Date;

if First.Date or last.Date then output;

Cheers

Barry

View solution in original post


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

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

What about

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 Smiley Happy

Super Contributor
Super Contributor
Posts: 3,174

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.

Suggested Google advanced search argument, this topic / post:

by group processing site:sas.com

Occasional Contributor
Posts: 11

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

Thank you Barry!

PROC Star
Posts: 7,366

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!

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;

 

Thanks in advance!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 11028 views
  • 0 likes
  • 6 in conversation