get the last row of a group with a condition

[Nasser_DRMCP]

Hello

I need to create a flag. the value should be 1 onfly for the last date among the staus S. here, only the row 3

thanks for your help

data have;
input client date status $ ;
informat date date9.;
format date date9.;
datalines ;
12345 10NOV2020 A
12345 11NOV2020 S
12345 12NOV2020 S
12345 12NOV2020 B
;

[PaigeMiller]

I get the feeling that the problem contains more elements than you told us. Are there more than one client? If yes, what do we do in that case? Is the data always properly sorted? Can we sort the data in order to produce the flag you want?

Can you show us a data set that illustrates the issues in the real data?

[mklangley]

Please clarify the rule: Are you saying that the last date with a status of S should be flagged 1? And should that be by client? If you would provide an expanded/more extensive example, that would help.

I don't know what your data represents, but what if, for example, this was the input data. Which records should be flagged?

data have;
input client date status $ ;
informat date date9.;
format date date9.;
datalines ;
12345 10NOV2020 A
12345 11NOV2020 S
12345 12NOV2020 S
12345 12NOV2020 B
12345 13NOV2020 S
23456 13NOV2020 S
34567 10NOV2020 S
34567 10NOV2020 B
34567 11NOV2020 S 
;

[Nasser_DRMCP]

Hello,

thanks for your quick respons

I am sorry, I figure out that I should clarify my problem.

I need to flag the last status S for each client and each day

in this new dataset with date and time, what I want is :

for the day 10NOV==>flag to 0

for the day 11NOV==>flag to 1

for the day 12NOV==>flag to 1 only for line 12:13:13

data have;

input client date :datetime18. status $ ;

informat date datetime20.;

format date datetime20.;

datalines ;

12345 10NOV2020:10:10:10 A

12345 11NOV2020:11:11:11 S

12345 12NOV2020:12:12:12 S

12345 12NOV2020:12:13:13 S

12345 12NOV2020:12:14:14 A

;

[mkeintz]

Leapfrogging on @PaigeMiller 's questions, I'll assume that the data are sorted by client/date.  If so, then:

data have;
input client date status $ ;
informat date date9.;
format date date9.;
datalines ;
12345 10NOV2020 A
12345 11NOV2020 S
12345 12NOV2020 S
12345 12NOV2020 B
;
data want (drop=_:);
  set have (where=(status='S')  in=ins) have (in=keep);
  by client;
  if first.client then call missing(_last_s_date);
  if ins then _last_s_date=date;
  retain _last_s_date;
  if keep;
  if date=_last_s_date and status='S' then flag=1;
  else flag=0;
run;

This program, for each client, "pre-reads" all the S status records and keeps the most recent date.  Then it reads all the records for the same client, it sets the flag when re-reading of the identified record.

[Nasser_DRMCP]

thanks mkeintz
I have clarified my problem. I hope it is clearer .
I do not manage to implement your suggestion
thanks for your help

[Nasser_DRMCP · Accepted solution]

thanks to your help, the solution is

data have;

input client date date9. datetime datetime20. status $ ;

informat datetime datetime20.;

format datetime datetime20. date date9.;

datalines ;

12345 10NOV2020 10NOV2020:10:10:10 A

12345 11NOV2020 11NOV2020:11:11:11 S

12345 12NOV2020 12NOV2020:12:12:12 S

12345 12NOV2020 12NOV2020:12:13:13 S

12345 12NOV2020 12NOV2020:12:14:14 A

92345 10NOV2020 10NOV2020:10:10:10 A

92345 11NOV2020 11NOV2020:11:11:11 S

92345 12NOV2020 12NOV2020:12:12:12 S

92345 12NOV2020 12NOV2020:12:13:13 S

92345 12NOV2020 12NOV2020:12:14:14 A

;

proc sort data=have; by client datetime ;run ;

　

data want (drop=_:);

set have (where=(status='S') in=ins) have (in=keep);

by client;

if first.client then call missing(_last_s_date);

if ins then _last_s_date=date;

retain _last_s_date;

if keep;

if date=_last_s_date and status='S' then flag=1;

else flag=0;

run;

data want(drop=_:) ;

format _last_s_datetime datetime20. ;

set have (where=(status='S') in=ins) have (in=keep);

by client date ;

if first.datetime then call missing(_last_s_datetime);

if ins then _last_s_datetime=datetime;

retain _last_s_datetime;

if keep;

if datetime=_last_s_datetime and status='S' then flag=1;

else flag=0;

run;

[novinosrin]

For What It's Worth, my share of fun-

data have;
input client date status $ ;
informat date date9.;
format date date9.;
datalines ;
12345 10NOV2020 A
12345 11NOV2020 S
12345 12NOV2020 S
12345 12NOV2020 B
;

proc sql;
 create table want as
 select *, status='S' and max(date)=date as flag
 from have
 group by client,status
 order by client, date;
quit;