BookmarkSubscribeRSS Feed
pp2014
Fluorite | Level 6

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

20 REPLIES 20
viveklanka
Fluorite | Level 6

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

pp2014
Fluorite | Level 6

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
Fluorite | Level 6

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
Fluorite | Level 6

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
Quartz | Level 8

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
Fluorite | Level 6

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
Fluorite | Level 6

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

Thanks Kurt

Ksharp
Super User

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
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 20 replies
  • 1636 views
  • 0 likes
  • 7 in conversation