Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- output problems

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-01-2013 04:25 PM

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 |

Accepted Solutions

Solution

10-02-2013
11:47 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to tmcrouse

10-02-2013 11:47 AM

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.

All Replies

Solution

10-02-2013
11:47 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to tmcrouse

10-02-2013 11:47 AM

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.