Help with getting Last Status Each month

[pp2014]

data have;

input id $  dt yyyymm8.  status $;

cards;

1 20141203  D

1 20141210  D

1 20141210  A

1 20141210  S

2 20150104  A

2 20150115  D

2 20150118  S

2 20150118  D

2 20150118  A

3 20150210  S

3 20150213  D

3 20150223  D

3 20150223  S

I want last status Each month for each id.

If there are 3 Statuses(A, D, S)  on the same day I want the following seq:  A first, S and then D.

So my output should look like:

ID  YrMn    Last_Status

1   201412     A

2   201501     A

3   201502     S

Thanks

[viveklanka]

Do you want the last status from each ID on the same day ? or do you want to sort the sequence. ?

[pp2014]

I want it based on the sequence if there are 3 statuses on same day.  But my output should for each ID each month what is last status. Thanks

[viveklanka]

So

as I understand

If there are three statuses on the same day then you want the last status of the month. for each ID.

then your ouput will be like.

1 20141210  S

2 20150118  A

as there are no three statuses for the ID 3 it wouldnt be included.,

Or even if there are no three statuses on the same day you want the last status of the user?

[pp2014]

My Output is as follow: ID  YrMn    Last_Status 1  201412    A 2  201501    A 3  201502    S I you look at the data data there were 3 statues for ID =3 on same day. I need above output based on sample data

[naveen_srini]

Not absolutely sure of your sample and your explanation, but just get started:
data have;

input id $  dt yymmdd10.  status $;
format dt yymmdd10.;

cards;
1 20141203  D
1 20141210  D
1 20141210  A
1 20141210  S
2 20150104  A
2 20150115  D
2 20150118  S
2 20150118  D
2 20150118  A
3 20150210  S
3 20150213  D
3 20150223  D
3 20150223  S
;

data want;
do until(last.id);
  set have;
  by id;
end;

format dt monyy5.;
run;

Regards,

Naveen Srinivasan

L&T Infotech

[pp2014]

Thanks Naveen. I want for each ID  each month their last status. SO if suppose ID 1 has in the month of December 3 statuses A, D,  S. I want last status for December in that seq (there may be in several days in the same month) but I want last status for that month based on seq.  There may be multiple Statuses on last day in the given month.  Also for the same ID = 1 if there may be data for Jan 2015, I want last status in Jan 2015 , or if there may be data for Feb 2015 I want last status for that ID in Feb 2015 and so on...

[pp2014]

data have; input id $  dt yymmdd10.  status $; format dt yymmdd10.; cards; 1 20141203  D 1 20141210  D 1 20141210  A 1 20141210  S 1 20150110  A 1 20150118  S 2 20150104  A 2 20150115  D 2 20150118  S 2 20150118  D 2 20150118  A 3 20150210  S 3 20150213  D 3 20150223  D 3 20150223  S ; If I have the data as above: I want last status Each month for each id.( There may be multiple months of data for one ID) If there are 3 Statuses(A, D, S)  on the same day I want the following seq:  A first, S and then D. So my output should look like: ID  YrMn    Last_Status 1  201412    A 1  201501    A 2  201501    A 3  201502    S

[pp2014]

data have;

input id $  dt yymmdd10.  status $;

format dt yymmdd10.;

cards;

1 20141203  D

1 20141210  D

1 20141210  A

1 20141210  S

1 20150110  A

1 20150118  S

2 20150104  A

2 20150115  D

2 20150118  S

2 20150118  D

2 20150118  A

3 20150210  S

3 20150213  D

3 20150223  D

3 20150223  S

;

I want for each month for each ID Last status . There may be multiple months of data for one ID.  If there are multiple statuses on the same day then I want the seq to be (A, S, and D).

Thanks

[Kurt_Bremser]

Create a new variable that contains the month of the date.

Sort by id, month and date (and by status if you have a preference which status to take when more than one happens on one single day).

Then use "if last.month;".

[pp2014]

Thanks Kurt.  I tried but I am not getting correct output.

Below is my code:

data have;

input id $  dt yymmdd10.  status $;

format dt yymmdd10.;

cards;

1 20141203  D

1 20141210  D

1 20141210  A

1 20141210  S

1 20150110  A

1 20150118  S

2 20150104  A

2 20150115  D

2 20150118  S

2 20150118  D

2 20150118  A

3 20150210  S

3 20150213  D

3 20150223  D

3 20150223  S

;

data have;

  set have;

  format mnth monyy5.;

  mnth = dt;

  if status = 'A' then seq = 1;

  else if status = 'S' then seq = 2;

  else if status = 'D'  then seq = 3;

run;

proc sort data = have;

  by id mnth descending seq;

run;

data want;

  set have;

  if last.mnth;

  by id mnth descending seq;

  keep id mnth status;

run;

I want the output as follows:

ID    mnth    Status

1    Dec14     A

1    Jan15      A

2    Jan15      A

3    Feb15      S

[Kurt_Bremser]

Your problem is here:

mnth = dt;

There you create just another date variable, only that the days are hidden by the format!

You need to create a month variable with

mnth = month(dt);

If years are also important, do

mnth = substr(put(dt,yymmddn8.),1,6);

Then do

proc sort data = have;

  by id mnth dt descending seq;

run;

data want;

  set have;

  by id mnth;

  if last.mnth;

  keep id mnth status;

run;

[pp2014]

Thanks Kurt

[Ksharp]

It is hard to understand what you want. Suppose I understand what you mean.

data have;
input id $  dt : yymmdd10.  status $;
format dt yymmdd10.;
cards;
1 20141203  D
1 20141210  D
1 20141210  A
1 20141210  S
1 20150110  A
1 20150118  S
2 20150104  A
2 20150115  D
2 20150118  S
2 20150118  D
2 20150118  A
3 20150210  S
3 20150213  D
3 20150223  D
3 20150223  S
;
data have;
  set have;
  year=year(dt);month=month(dt);
  if status = 'A' then seq = 1;
  else if status = 'S' then seq = 2;
  else if status = 'D'  then seq = 3;
run;
proc sort data=have;by id year month dt descending seq;run;
data want;
 set have;
 by id year month ;
 if last.month;
 format dt monyy7.;
 drop year month seq;
run;

Xia Keshan

[Haikuo]

Let me rephrase your rules, correct me if I am wrong:

1. You want the last status of each month, of each id. so you will have an output on id/month level.

2. if multiple status occur on the last available day of certain month, the status will be chosen by this order: A, S, D.

So in summary, for the same id, same month,  if status occurs on different days, the last day triumphs; if they happen on the same last day, A triumphs S triumphs D.

In your data,

1 20150110 A

1 20150118 S

per your rules, Shouldn't 'S' be the last status? However, you present this as your outcome:

1 Jan15 A

I must ask WHY.  Please sort out your rules before everyone here breaks their legs running circles.

Thanks,

Haikuo