Finding Earliest time with Separate Month Year Variables

[CydneyLB]

Hello,

I currently have a data set of events with the month and year recorded for each event. For each person i want to find which event happens first and then find the year and month of this event.

Example data

ID     E1   M1      Y1          E2   M2      Y2 

001    1       7    1999        0      .         .

002    1       1     2004       1       9       2001

003    1       3     2006       1      11      2006

etc...

Want

FirstE      FirstMonth   FirstYear

E1                  7                 1999

E2                  9                  2001

E1                  3                  2006

The code I have currently been trying i cant get to work so any suggestions would be really helpful!

data want;
 set have;

   Year = min(Y1, Y2);

   array Years (*) Y1 Y2;
   array Months (*) M1 M2;

  	if Year = Y1 = Y2 then 
                FirstE = vname(Months[whichn(smallest(1,of Months[*]), of Months[*])]); else
	if Y1 ^= Y2 then 
                FirstE = vname(Years[whichn(smallest(1,of Years[*]), of Years[*])]);

     if FirstE = "E1" then do;
                   FirstMonth = M1; FirstYear = Y1; end; else
     if FirstE = "E2" then do;
                   FirstMonth = M2; FirstYear = Y2; end;
RUN;

Thanks

Cydney

[PGStats · Accepted solution]

Use function mdy() and take proper care of missing values:

data want;
set have;
d1 = coalesce(mdy(M1, 1, Y1), constant("BIG"));
d2 = coalesce(mdy(M2, 1, Y2), constant("BIG"));
if d1 <= d2 then do;
    FirstE = "E1";
    firstYear = Y1;
    FirstMonth = M1;
    end;
 else if d1 > d2 then do;
    FirstE = "E2";
    firstYear = Y2;
    FirstMonth = M2;
    end;
drop d1 d2;
run;

(untested)

[CydneyLB]

Thank you for commenting!

I was trying to avoid creating a date variable (for later calculations) but that will work.

Thanks 🙂

Cydney

[novinosrin]

FWIW

data have;
Input ID $     E1   M1      Y1          E2   M2      Y2 ;
cards;
001    1       7    1999        0      .         .
002    1       1     2004       1       9       2001
003    1       3     2006       1      11      2006
;

data temp;
 set have;
 array t(2,3) E1--y2;
 array j FirstE      FirstMonth   FirstYear;
 do _i=1 to 2;
  call missing(temp);
  do _k=1 to 3;
   j(_k)=t(_i,_k);
  end;
  if n(of j(*))=dim(j) then temp=mdy(firstmonth,1,firstyear);
  output;
 end;
keep first: temp id;
run;
proc sql;
create table want(drop=temp) as
select *
from temp
group by id
having temp=min(temp);
quit;

[Jagadishkatam]

Alternatively try

data want;
set have;
array event(*) E1 E2;
array months(*) M1 M2;
array years(*) Y1 Y2;
do i = 1 to dim(event);
if event(i) ne . then e=vname(event(i));
if month(i) ne . then m=months(i);
if year(i) ne . then y=years(i);
output;
end;
run;

proc sort data=want;
by id y m;
where y ne .;
run;

data want2;
set want;
by id y m ;
if first.id;
keep id e m y;
run;

[CydneyLB]

This solution is nice and simple thanks Jag