topic Re: Help with getting Last Status Each month in SAS Procedures

Help with getting Last Status Each month

pp2014 — Fri, 27 Mar 2015 07:59:43 GMT

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

Re: Help with getting Last Status Each month

viveklanka — Fri, 27 Mar 2015 08:22:49 GMT

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

Re: Help with getting Last Status Each month

pp2014 — Fri, 27 Mar 2015 08:44:39 GMT

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

Re: Help with getting Last Status Each month

viveklanka — Fri, 27 Mar 2015 08:54:28 GMT

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?

Re: Help with getting Last Status Each month

naveen_srini — Fri, 27 Mar 2015 09:48:06 GMT

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

Re: Help with getting Last Status Each month

pp2014 — Fri, 27 Mar 2015 09:49:38 GMT

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

Re: Help with getting Last Status Each month

pp2014 — Fri, 27 Mar 2015 09:59:54 GMT

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...

Re: Help with getting Last Status Each month

pp2014 — Fri, 27 Mar 2015 10:42:52 GMT

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

Re: Help with getting Last Status Each month

pp2014 — Fri, 27 Mar 2015 10:46:34 GMT

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

Re: Help with getting Last Status Each month

Kurt_Bremser — Fri, 27 Mar 2015 11:54:39 GMT

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;".

Re: Help with getting Last Status Each month

pp2014 — Fri, 27 Mar 2015 12:29:03 GMT

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

Re: Help with getting Last Status Each month

Kurt_Bremser — Fri, 27 Mar 2015 12:41:19 GMT

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;

Re: Help with getting Last Status Each month

pp2014 — Fri, 27 Mar 2015 12:48:52 GMT

Thanks Kurt

Re: Help with getting Last Status Each month

Ksharp — Fri, 27 Mar 2015 13:04:05 GMT

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

Re: Help with getting Last Status Each month

Haikuo — Fri, 27 Mar 2015 13:23:03 GMT

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

Re: Help with getting Last Status Each month

shubhayog — Fri, 27 Mar 2015 15:48:00 GMT

Hi pp2014,

I guess this will help you,

data have;

input id $ dt yymmdd10. 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

;

run;

data int1;

set have;

if status eq 'D' then temp1=1;

if status eq 'S' then temp1=2;

if status eq 'A' then temp1=3;

run;

proc sort

data=int1;

by id dt temp1;

run;

data int2;

set int1;

if last.id then temp2=1;

by id;

run;

data want;

format dt monyy7.;

set int2;

where temp2=1;

drop temp1 temp2;

run;

Regards,

Yogesh

Re: Help with getting Last Status Each month

pp2014 — Fri, 27 Mar 2015 20:21:09 GMT

Thanks lot everybody for help...

Re: Help with getting Last Status Each month

Haikuo — Fri, 27 Mar 2015 20:24:37 GMT

Have you got your correct answer? if yes, please mark it. If no, please explain why. BTW, I have raised a question in order to help you, still waiting for your response.

Re: Help with getting Last Status Each month

pp2014 — Fri, 27 Mar 2015 23:30:26 GMT

Hi Hai.Kuo,

I got it working using following 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;

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

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;

Output was as follows:

ID mnth Status

1 201412 A

1 201501 A

2 201501 A

3 201502 S

Re: Help with getting Last Status Each month

Haikuo — Sat, 28 Mar 2015 00:51:26 GMT

Thank you for getting back to me. So from reading your code, what I have gathered is that status are evaluated on monthly basis, you don't really care what status happens on which day (therefore, using Last status is kinda misleading, and discussing multiple status on the same day is meaningless) rather, you only want to know if there is A, S or D in certain month, and output A if there is A, else output S if there is S but no A, output D there is D with no A/D. Let me present you more extreme example that your code does:

data have;

input id $ dt yymmdd10. status $;

format dt yymmdd10.;

cards;

1 20141203 A

1 20141210 D

1 20150110 A

1 20150118 S

2 20150104 A

2 20150115 D

2 20150118 D

3 20150210 A

3 20150213 S

3 20150223 S

;

data have;

set have;

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

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;

And this is what you are getting:

1	1	A	201412
2	1	A	201501
3	2	A	201501
4	3	A	201502

Meaning as long as there is a 'A' occurred within a month, regardless which day of the month, first day or last day, regardless whether there are 'S' or 'D', your code will ALWAYS output it. Make sure this is what you want.

And if indeed this is what you need, you can also simply resort to a SQL solution:

proc sql;

select id, put(dt,yymon7.) as yymon, case when sum(status='A')>0 then 'A'

when sum(status='S')>0 then 'S' when sum(status='D')>0 then 'D' end as status

from have

group by id, calculated yymon;

quit;