How to -> Order Values by Date in New Column

[turcay]

Hello everyone,

I have a sample dataset as below. I want to add new variable called “Period” on new dataset. If the DATE column includes ”DEC” value then I want to add “T” values on Period column by grouping ID column but we should consider that there can be many ‘DEC’ values on DATE. For this reason, latest date will be taken “T” value, second date will be taken “T_1”, third date will be taken “T_2” and so on. If DATE column includes different value from “DEC” we will write in Period column “T_Break” value. Actually, I created my desired output but It seems to me I extended the method. Can somebody show more easy method or  give information me ,please ?

Data Have;
Length ID 8;
INFILE DATALINES MISSOVER;
INPUT ID Date;
INFORMAT Date Date9.;
FORMAT Date Date9.;
DATALINES;
100 31dec2011
102 31jul2014
100 31dec2012
100 31mar2014
101 31dec2015
101 31dec2013
101 31dec2014
100 31dec2013
102 31dec2010
102 31dec2013
102 31dec2011
102 31dec2012
;
RUN;
data Have2;
set Have;
if Month(date) ne 12 then Marker="BREAK";
run;
proc sort data=Have2; 
by ID Marker descending date;
run;
data Have3;
set Have2;
by ID Marker;
retain Count;

if first.marker then Count=0;
else Count=Count+1;

if Count ne 0 then Period=catx("_","T",put(Count,1.));
else if Count eq 0 then Period="T";

if Marker="BREAK" then Period="T_BREAK";
run;

Thank you.

[Reeza]

You can move the logic from identifying the break to your final data set, so you don't have two passes through the data. Otherwise there's not much to change.

[turcay]

You mean Work.Have2 is not necessary. But I couldn't handle it I wrote the following code to decrease data step but I couldn't succeed.

/*data Have2;
set Have;
if Month(date) ne 12 then Marker="BREAK";
run;
*/

proc sort data=Have; 
by ID /*Marker*/ descending date;
run;
data Have3;
set Have;
if Month(date) ne 12 then Marker="BREAK";
/*set Have*/
by ID Marker;
retain Count;

if first.marker then Count=0;
else Count=Count+1;

if Count ne 0 then Period=catx("_","T",put(Count,1.));
else if Count eq 0 then Period="T";

if Marker="BREAK" then Period="T_BREAK";
run;

Thank you.

[RW9 · Accepted solution]

Something like:

data have;
  set have;
  mnth=month(date);
  year=year(date);
run;

proc sort data=have;
  by id mnth descending year;
run;

data want (drop=mnth year);
  set have;
  by id mnth descending year;
  retain count;
  length period $20;
  if first.id then count=0;
  if first.mnth and mnth=12 then period="T";
  else if mnth=12 then do;
    count=count+1;
    period=cats("T_",put(count,best.));
  end;
  else period="T_BREAK";
run;

[turcay]

Thank you @RW9 , This is the answer which I wanted.