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
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)
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)
Thank you for commenting!
I was trying to avoid creating a date variable (for later calculations) but that will work.
Thanks 🙂
Cydney
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;
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;
This solution is nice and simple thanks Jag
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: