BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
corella
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
twocanbazza
Quartz | Level 8

What about

by ID Date;

if First.Date or last.Date then output;

Cheers

Barry

View solution in original post

10 REPLIES 10
twocanbazza
Quartz | Level 8

What about

by ID Date;

if First.Date or last.Date then output;

Cheers

Barry

corella
Calcite | Level 5

Thanks! It was so simple Smiley Happy

sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10

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
Calcite | Level 5

Thank you Barry!

art297
Opal | Level 21

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
Calcite | Level 5

It worked! Thank you!

vraj1
Quartz | Level 8

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
Calcite | Level 5

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
Quartz | Level 8

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
Calcite | Level 5

 

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!

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 10 replies
  • 40962 views
  • 0 likes
  • 7 in conversation