I have a table with 147k rows. I am trying to take the column that is the X and say that is my beginning point. So, if x=1 then I have a do statement. I make the the date +1 and the date +90 because any of the events that fall between 1 and 90 days from the original X need to be counted by the event and location. If I look at the first ID 104534, I see X took place on 6/6/2012. After that, there were events that occured in event1 and location1, however not necessarily by ID 104534. I need to count the events and location that took place and then place those in 104534's bucket. Then every ID that follows, I need to do the same. I tried to code how I need this and hope posting the code might offer some baseline. I am also posting an example of the data I have, the results when I run my code as written and what I need.
DATA HAVE | ||||||||||
MEM | date | ID | X | event1 | event2 | event3 | event4 | location1 | location2 | location3 |
111 | 6/6/2012 | 104534 | 1 | 1 | 1 | |||||
111 | 6/6/2012 | 623422 | 1 | 1 | ||||||
111 | 6/28/2012 | 623256 | 1 | 1 | 1 | |||||
111 | 6/28/2012 | 623422 | 1 | 1 | ||||||
111 | 8/31/2012 | 104534 | 1 | 1 | ||||||
111 | 8/31/2012 | 623422 | 1 |
Code:
data eventfinal;
set event nobs=last_obs;
by mem ;
if X=1 then do;
wb = date + 1;
we = date + 90;
do pt = _N_ + 1 to last_obs;
set event(keep= mem date event1 event2 event3 event4 location1 location2 location3
rename=(date=dt))
point=pt;
if mem ne mem or dt > we then leave;
if dt >= wb and dt<=we and location1>=1 and event1>=1 then loc1ev1 = (event1);
if dt >= wb and dt<=we and location2>=1 and event1>=1 then loc2ev1= (event1);
if dt >= wb and dt<=we and location3>=1 and event1>=1 then loc3ev1= (event1);
if dt >= wb and dt<=we and location1>=1 and event2>=1 then loc1ev2= (event2);
if dt >= wb and dt<=we and location2>=1 and event2>=1 then loc2ev2= (event2);
if dt >= wb and dt<=we and location3>=1 and event2>=1 then loc3ev2= (event2);
if dt >= wb and dt<=we and location1>=1 and event3>=1 then loc1ev3= (event3);
if dt >= wb and dt<=we and location2>=1 and event3>=1 then loc2ev3= (event3);
if dt >= wb and dt<=we and location3>=1 and event3>=1 then loc3ev3= (event3);
if dt >= wb and dt<=we and location1>=1 and event4>=1 then loc1ev4= (event4);
if dt >= wb and dt<=we and location2>=1 and event4>=1 then loc2ev4= (event4);
if dt >= wb and dt<=we and location3>=1 and event4>=1 then loc3ev4= (event4);
end;
output;
end;
if we =. then we=date;
if wb=' ' then wb=date;
if we=wb then output;
run;
RESULTS | |||||||||||||
MEM | ID | loc1ev1 | loc2ev1 | loc3ev1 | loc1ev2 | loc2ev2 | loc3ev2 | loc1ev3 | loc2ev3 | loc3ev3 | loc1ev4 | loc2ev4 | loc3ev4 |
111 | 104534 | 1 | |||||||||||
111 | 623422 | 1 | |||||||||||
111 | 623256 | 1 | |||||||||||
111 | 623422 | 1 | |||||||||||
111 | 104534 | ||||||||||||
111 | 623422 |
DATA NEED | |||||||||||||
MEM | ID | loc1ev1 | loc2ev1 | loc3ev1 | loc1ev2 | loc2ev2 | loc3ev2 | loc1ev3 | loc2ev3 | loc3ev3 | loc1ev4 | loc2ev4 | loc3ev4 |
111 | 104534 | 2 | |||||||||||
111 | 623422 | 2 | |||||||||||
111 | 623256 | 1 |
I found the answer on my own by playing around with the code. I found that if I add =sum(loc1ev1,event1) the summation turns out correctly. Of course I have to change it correctly for each if statement. EX. =sum(loc2ev1,event1) and so on.
I found the answer on my own by playing around with the code. I found that if I add =sum(loc1ev1,event1) the summation turns out correctly. Of course I have to change it correctly for each if statement. EX. =sum(loc2ev1,event1) and so on.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.