DATA Step, Macro, Functions and more

How to -> Order Values by Date in New Column

Accepted Solution Solved
Reply
Super Contributor
Posts: 395
Accepted Solution

How to -> Order Values by Date in New Column

[ Edited ]

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.


Accepted Solutions
Solution
‎12-17-2015 07:19 AM
Super User
Super User
Posts: 7,970

Re: How to -> Order Values by Date in New Column

[ Edited ]

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;

View solution in original post


All Replies
Super User
Posts: 19,822

Re: How to -> Order Values by Date in New Column

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.
Super Contributor
Posts: 395

Re: How to -> Order Values by Date in New Column

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.

Solution
‎12-17-2015 07:19 AM
Super User
Super User
Posts: 7,970

Re: How to -> Order Values by Date in New Column

[ Edited ]

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;
Super Contributor
Posts: 395

Re: How to -> Order Values by Date in New Column

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

 

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 305 views
  • 2 likes
  • 3 in conversation