output problems

tmcrouse — Tue, 01 Oct 2013 20:25:19 GMT

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

loc1ev1

loc2ev1

loc3ev1

loc1ev2

loc2ev2

loc3ev2

loc1ev3

loc2ev3

loc3ev3

loc1ev4

loc2ev4

loc3ev4

111

104534

111

623422

111

623256

Re: output problems

tmcrouse — Wed, 02 Oct 2013 15:47:38 GMT

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.

topic output problems in SAS Procedures

output problems

Re: output problems