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
Do you want the last status from each ID on the same day ? or do you want to sort the sequence. ?
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
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?
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
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
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...
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
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
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;".
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
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;
Thanks Kurt
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.