topic get the last row of a group with a condition in SAS Programming

get the last row of a group with a condition

Nasser_DRMCP — Tue, 01 Dec 2020 17:54:34 GMT

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
;

Re: get the last row of a group with a condition

PaigeMiller — Tue, 01 Dec 2020 18:03:56 GMT

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?

Re: get the last row of a group with a condition

mklangley — Tue, 01 Dec 2020 18:13:13 GMT

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 
;

Re: get the last row of a group with a condition

mkeintz — Tue, 01 Dec 2020 18:38:21 GMT

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.

Re: get the last row of a group with a condition

novinosrin — Tue, 01 Dec 2020 18:25:42 GMT

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;

Re: get the last row of a group with a condition

Nasser_DRMCP — Wed, 02 Dec 2020 09:14:03 GMT

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

;

Re: get the last row of a group with a condition

Nasser_DRMCP — Wed, 02 Dec 2020 09:44:39 GMT

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

Re: get the last row of a group with a condition

Nasser_DRMCP — Wed, 02 Dec 2020 10:05:54 GMT

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;