SAS Support Communities

r4321 · ‎05-08-2021

Kurt, thanks for your help on this. I am still having issues getting it just right. Specifically, the problem is that 'the want' spits out time1 and time2 values that aren't centered at zero. For example, time1 time2 8 26 26 42 instead of time1 time2 0 18 18 34 I wrote some code that spits out what I want better, but I still don't know how to insert in the timesince variables that I want. Could you advise? proc sort data=have out=have_sorted; by ID County Time FocalEvent; run; data have_group; do i=1 until (last.FocalEvent); set have_sorted; by ID County FocalEvent notsorted; if lag(FocalEvent)=1 then group+1; if first.ID then group=1; end; run; data have_group2; set have_group; by ID County group notsorted; if first.County then nobs=1; else nobs+1; run; proc means data=have_group2 noprint; var FocalEvent X1mva X2police nobs; class ID County group; ways 3; output out=have_sum sum= min= max= / autoname; run; data want; set have_sum (keep=ID County FocalEvent_Sum X1mva_Sum X2police_Sum nobs_Min nobs_Max); if nobs_Min ne 1 then Time1 = nobs_Min-1; else Time1 = nobs_Min; rename FocalEvent_Sum = Event X1mva_Sum = X1mva X2police_Sum = X2police nobs_Max = Time2; run;

r4321 · ‎04-20-2021

Thank you for your response. Greatly appreciated. I set up the code like so: data test.agmagtest; set test.agmag2 (rename=(X1mva=_X1mva X2police=_X2police)); by id; retain X1mva X2police time1 ; if first.id then do; X1mva = 0; X2police = 0; time1 = time; end; X1mva + _X1mva; X2police + _X2police; if focalevent = 1 or last.id then do; time2 = time; event = focalevent; output; X1mva = 0; X2police = 0; time1 = time; end; if X1mva = 1 then timesinceX1 = 0; else timesinceX1 + 1; if X2police = 1 then timesinceX2 = 0; else timesinceX2 + 1; drop _: time focalevent; run; A couple issues I am having: (1) I don't get sums, mins. and maxes for the x1 and x2 variables (2) The timesinceX1 and timesinceX2 variables are producing impossible values. I only want it to tell me the time since the X1 or the time since the last X2 for that particular ID. Could you please advise? Thanks a lot.

r4321 · ‎04-20-2021

Hello all, I have code that allows me to reorganize my data (companies observed each year 2006-2015) based on a variable. The variable is an event-based variable that takes on values from 0-1 (1 if it happened). When (and/or when) the variable takes on a 1, it collapses the rows and sum the values on other relevant variables. It also creates different indicators where: (1) one variable indicates when time starts (2) one variable that indicates when the time ends, and (3) a variable that indicates 0/1 whether an event took place at all. Essentially, each observation of a case becomes a start-stop as to when the event-based variable was observed. I Illustrate below what I have: Have: Time ID County FocalEvent X1mva X2police 1 222 ABC 1 10 15 2 222 ABC 0 5 10 3 222 ABC 0 15 5 4 222 ABC 0 2 4 5 222 ABC 1 5 10 6 222 ABC 0 10 5 7 222 ABC 0 5 10 8 222 ABC 0 4 4 9 222 ABC 0 5 5 10 222 ABC 1 10 10 1 333 BBB 0 3 4 2 333 BBB 0 2 1 3 333 BBB 0 5 5 4 333 BBB 1 10 5 5 333 BBB 0 15 15 6 333 BBB 0 10 10 7 333 BBB 0 10 10 8 333 BBB 1 5 5 9 333 BBB 0 10 10 10 333 BBB 0 15 15 1 444 CCC 0 1 1 2 444 CCC 0 5 5 3 444 CCC 0 4 4 4 444 CCC 0 5 5 5 444 CCC 0 1 1 6 444 CCC 1 5 5 7 444 CCC 0 10 10 8 444 CCC 0 5 5 9 444 CCC 0 5 5 10 444 CCC 0 5 5 1 555 DDD 0 1 1 2 555 DDD 0 1 1 3 555 DDD 0 1 1 Want: ID County X1mva X2police Time1 Time2 Event 222 ABC 10 15 1 1 1 222 ABC 27 29 1 5 1 222 ABC 34 34 5 10 1 333 BBB 20 15 1 4 1 333 BBB 40 40 4 8 1 333 BBB 25 25 8 10 0 444 CCC 21 21 1 6 1 444 CCC 25 25 6 10 0 555 DDD 3 3 1 3 0 Code: data have; input Time ID County :$ FocalEvent X1mva X2police; cards; 1 222 ABC 1 10 15 2 222 ABC 0 5 10 3 222 ABC 0 15 5 4 222 ABC 0 2 4 5 222 ABC 1 5 10 6 222 ABC 0 10 5 7 222 ABC 0 5 10 8 222 ABC 0 4 4 9 222 ABC 0 5 5 10 222 ABC 1 10 10 1 333 BBB 0 3 4 2 333 BBB 0 2 1 3 333 BBB 0 5 5 4 333 BBB 1 10 5 5 333 BBB 0 15 15 6 333 BBB 0 10 10 7 333 BBB 0 10 10 8 333 BBB 1 5 5 9 333 BBB 0 10 10 10 333 BBB 0 15 15 1 444 CCC 0 1 1 2 444 CCC 0 5 5 3 444 CCC 0 4 4 4 444 CCC 0 5 5 5 444 CCC 0 1 1 6 444 CCC 1 5 5 7 444 CCC 0 10 10 8 444 CCC 0 5 5 9 444 CCC 0 5 5 10 444 CCC 0 5 5 1 555 DDD 0 1 1 2 555 DDD 0 1 1 3 555 DDD 0 1 1 ; run; proc sort data = have; by ID County Time; run; data want1; set have; by ID; category + first.ID; output; category + FocalEvent; run; proc sql; create table want2 as select ID, County, sum(X1mva) as X1mva, sum(X2police) as X2police, max(min(Time)-1,1) as Time1, max(Time) as Time2, max(FocalEvent) as Event from want1 group by ID, County, category order by ID, County, Time1, Time2 ; quit; proc print; run; In practice, the variables (e.g., X1MVA, X2POLICE) that I sum have a lot of zeros on them and the time based events are spread out and more sparse. Is there a way I can add to my code such that I can count the time since the latest occurrence of the X1MVA or X2POLICE variables that I sum. For instance: Have: Time ID County FocalEvent X1mva X2police 1 222 ABC 1 0 1 2 222 ABC 0 1 0 3 222 ABC 0 0 1 4 222 ABC 0 0 0 5 222 ABC 1 0 0 6 222 ABC 0 0 1 7 222 ABC 0 0 0 8 222 ABC 0 0 0 9 222 ABC 0 1 0 10 222 ABC 1 1 0 1 333 BBB 0 0 1 2 333 BBB 0 0 1 3 333 BBB 0 1 0 4 333 BBB 1 0 0 5 333 BBB 0 1 0 6 333 BBB 0 0 0 7 333 BBB 0 0 0 8 333 BBB 1 0 1 9 333 BBB 0 0 1 10 333 BBB 0 1 0 Want: ID County X1mva X2police timesinceX1 timesinceX2 Time1 Time2 Event 222 ABC 0 1 0 0 1 1 1 222 ABC 1 1 3 2 1 5 1 222 ABC 2 1 0 4 5 10 1 333 BBB 1 2 1 2 1 4 1 333 BBB 1 1 3 0 4 8 1 333 BBB 1 1 0 1 8 10 0 Thanks in advance and I greatly appreciate you taking a look.

r4321 · ‎07-29-2020

Hello Ballard, I have updated my example code as per your instructions.

r4321 · ‎07-29-2020

Anyone have some suggestions? Thanks!

r4321 · ‎07-27-2020

data have; input time Bucketcode State $ Count; datalines; 101 222 OH 3 102 222 OH 4 103 222 OH 4 103 333 OH 1 104 333 OH 1 101 111 TX 2 102 111 TX 1 103 111 TX 1 104 111 TX 2 101 222 VA 1 102 222 VA 2 103 222 VA 2 104 222 VA 4 104 444 VA 2 ; data want; input Time Bucketcode State $ Countdiff; datalines; 102 222 OH 1 103 222 OH 0 103 333 OH 1 104 222 OH -4 104 333 OH 0 102 111 TX -1 103 111 TX 0 104 111 TX 1 102 222 VA 1 103 222 VA 0 104 222 VA 2 104 444 VA 2 ; Hello Ballard, yes, it is fine that the first state bucketcode is dropped as long as it appears in that first time period (i.e., 101). Appearances/disappearances of bucketcodes in subsequent time periods should be treated differently. If a new bucketcode appears in a time period (eg., 102) (that isn't the first) then that should be reflected in the changescore differential for that bucketcode/state/timeperiod triplet with whatever the addition in count was, eg., 1. If a bucket that was there in a time period disappears in a subsequent period (was there in 102, but not in a state-time-bucketcode triplet in the next period, eg., 103) then the state-time-bucketcode triplet should reflect whatever the drop in value is/was for that subsequent period. For example, VA-104-333 has a count of 2, but VA-105-333 is not there then VA-105-333 would be -2. Thank you again for your help.

r4321 · ‎07-27-2020

Ballard, thank you for your quick reply. Your comment and code brought some things to my attention that I hadn't considered. I updated the notes to reflect these realizations. Essentially, I do need the code to accomodate: (1) I need it to accommodate multiple bucketcodes per state per time period. (2) I need to account for the appearance / disappearance of bucketcodes within a state. For example, a new bucketcode might enter OH at time period 103 that wasnt previously there in the prior time period (102). Conversely, a bucketcode that was there in OH during time period 103 might exit that state and not be there in time period 104. In both cases, the appearance / disappearances need to be marked in the change variable (e.g., +1, -4, etc.; see example). (3) Dropping the first time period is fine, but after that if a bucketcode enters a state and time period, that wasn't previously there, it should be treated as entering the dataset. Thank you again

r4321 · ‎07-26-2020

Updated (7-27-20) Hello all, I could use your help figuring out some code. I have different 'buckets' that take on different values depending on the time, bucketcode, and state, and I am trying to compute the change in values (count) across time and state. Please see an illustration of my data below. A couple new notes: (1) I need it to accommodate multiple bucketcodes per state per time period. (2) I need to account for the appearance / disappearance of bucketcodes within a state. For example, a new bucketcode might enter OH at time period 103 that wasnt previously there in the prior time period (102). If a new bucketcode appears in a time period (eg., 102) (that isn't the first) then that should be reflected in the changescore differential for that bucketcode/state/timeperiod triplet with whatever the addition in count was, eg., 1. If a bucket that was there in a time period disappears in a subsequent period (was there in 102, but not in a state-time-bucketcode triplet in the next period, eg., 103) then the state-time-bucketcode triplet should reflect whatever the drop in value is/was for that subsequent period. For example, VA-104-333 has a count of 2, but if VA-105-333 is not there then VA-105-333 would be -2. (3) Dropping the first time period is fine, but after that if a bucketcode enters a state and time period, that wasn't previously there, it should be treated as entering the dataset. data have; input time Bucketcode State $ Count; datalines; 101 222 OH 3 102 222 OH 4 103 222 OH 4 103 333 OH 1 104 333 OH 1 101 111 TX 2 102 111 TX 1 103 111 TX 1 104 111 TX 2 101 222 VA 1 102 222 VA 2 103 222 VA 2 104 222 VA 4 104 444 VA 2 ; data want; input Time Bucketcode State $ Countdiff; datalines; 102 222 OH 1 103 222 OH 0 103 333 OH 1 104 222 OH -4 104 333 OH 0 102 111 TX -1 103 111 TX 0 104 111 TX 1 102 222 VA 1 103 222 VA 0 104 222 VA 2 104 444 VA 2 ; Thanks!

r4321 · ‎06-04-2020

Updated the question Thanks, Rich

r4321 · ‎06-03-2020

Arthur, thanks for your attention and help. Still not quite getting what I need out of the code. I think the fault is on my end though as I need to rethink how to approach my dataset. Instead of trying to add variables to widen the dataset and keep it the same number of observations, I think I need to adapt a different approach. Essentially, I think I need to duplicate sets of observations to create windows of treated and non-treated counties around the focal events I am interested in examining. I also like to add a few other variables: (1) a variable that indicates which treatment number this is for a county (i.e., first treatment, second treatment, …) (2) a variable that indicates whether a county was treated within a short time window of a previous time it was treated (=1 if treated within <=5 time periods, 0 otherwise) and (3) a variable that indicates the degree of treatment (I want the focal event variable to be binary in the wantdata but in the have data it is a actually >1 sometimes since it is a count variable), so if focal event was 2, for example, this variable would = 2. See below for example. Also, I should note that is a focal occurred before week 6, the durations for those observations may not be able to get up to the full 5 weeks before and after. For example, if a focal event occurred in a county in week 2, I'll only have the 1 week duration for that focal event. Thanks again Have: Time ID County FocalEvent 1 222 ABC 0 2 222 ABC 0 3 222 ABC 0 4 222 ABC 0 5 222 ABC 1 6 222 ABC 0 7 222 ABC 0 8 222 ABC 0 9 222 ABC 0 10 222 ABC 0 11 222 ABC 0 12 222 ABC 0 13 222 ABC 0 1 333 BBB 0 2 333 BBB 0 3 333 BBB 0 4 333 BBB 0 5 333 BBB 0 6 333 BBB 0 7 333 BBB 0 8 333 BBB 0 9 333 BBB 0 10 333 BBB 0 11 333 BBB 0 12 333 BBB 0 13 333 BBB 0 1 444 CCC 0 2 444 CCC 0 3 444 CCC 0 4 444 CCC 0 5 444 CCC 1 6 444 CCC 0 7 444 CCC 0 8 444 CCC 0 9 444 CCC 0 10 444 CCC 1 11 444 CCC 0 12 444 CCC 0 13 444 CCC 0 1 555 DDD 0 2 555 DDD 0 3 555 DDD 0 4 555 DDD 0 5 555 DDD 0 6 555 DDD 0 7 555 DDD 0 Want: Time ID County FocalEvent EventWeek Duration BeforeAfter Treated Tnumber Trecent Tdegree 4 222 ABC 0 5 1 0 1 1 0 0 5 222 ABC 1 5 1 1 1 1 0 0 4 333 BBB 0 5 1 0 0 0 0 0 5 333 BBB 0 5 1 1 0 0 0 0 4 444 CCC 0 5 1 0 1 1 0 0 5 444 CCC 1 5 1 1 1 1 0 0 4 555 DDD 0 5 1 0 0 0 0 0 5 555 DDD 0 5 1 1 0 0 0 0 9 444 CCC 0 10 1 0 1 2 0 0 10 444 CCC 1 10 1 1 1 2 0 0 9 333 BBB 0 10 1 0 0 0 0 0 10 333 BBB 0 10 1 1 0 0 0 0 3 222 ABC 0 5 2 0 1 1 0 0 4 222 ABC 0 5 2 0 1 1 0 0 5 222 ABC 1 5 2 1 1 1 0 0 6 222 ABC 0 5 2 1 1 1 0 0 3 333 BBB 0 5 2 0 0 0 0 0 4 333 BBB 0 5 2 0 0 0 0 0 5 333 BBB 0 5 2 1 0 0 0 0 6 333 BBB 0 5 2 1 0 0 0 0 3 444 CCC 0 5 2 0 1 1 0 0 4 444 CCC 0 5 2 0 1 1 0 0 5 444 CCC 1 5 2 1 1 1 0 0 6 444 CCC 0 5 2 1 1 1 0 0 3 555 DDD 0 5 2 0 0 0 0 0 4 555 DDD 0 5 2 0 0 0 0 0 5 555 DDD 0 5 2 1 0 0 0 0 6 555 DDD 0 5 2 1 0 0 0 0 8 444 CCC 0 10 1 0 1 2 0 0 9 444 CCC 0 10 1 0 1 2 0 0 10 444 CCC 1 10 1 1 1 2 0 0 11 444 CCC 1 10 1 1 1 2 0 0 8 333 BBB 0 10 1 0 0 0 0 0 9 333 BBB 0 10 1 0 0 0 0 0 10 333 BBB 0 10 1 1 0 0 0 0 11 333 BBB 0 10 1 1 0 0 0 0

r4321 · ‎06-03-2020

This is wonderful. Thank you!

r4321 · ‎06-03-2020

Thanks Bart

r4321 · ‎06-03-2020

Bart, Thank you for your help! Going through this now and hoping to ask a follow-up. Yes, you were correct that I needed the time periods like so (i.e. for 222 you want [1,1], [1,5],[5,10] and the sums like so [1,1],[2,5][6,10]) . So far this code works great, but how can I set the code such that time1 on the first observation of each set of observations (or IDs) code has a 0 instead of a 1? See below for reference: Time ID County FocalEvent X1mva X2police 1 222 ABC 1 10 15 2 222 ABC 0 5 10 3 222 ABC 0 15 5 4 222 ABC 0 2 4 5 222 ABC 1 5 10 6 222 ABC 0 10 5 7 222 ABC 0 5 10 8 222 ABC 0 4 4 9 222 ABC 0 5 5 10 222 ABC 1 10 10 Want: ID County X1mva X2police Time1 Time2 Event 222 ABC 10 15 0 1 1 222 ABC 27 29 1 5 1 222 ABC 34 34 5 10 1 Thanks again, R

r4321 · ‎05-28-2020

Hello all, I have data on companies observed each year 2000-2015 (16 years) where I am looking to reorganize it based on a variable. This variable is an event-based variable that takes on values from 0-1 (1 if it happened). If (and/or when) the variable takes on a 1, I would like to collapse the rows and sum the values on other relevant variables. I would also like to create different indicators based on this: (1) one variable indicates when time starts (2) one variable that indicates when the time ends, and (3) a variable that indicates 0/1 whether an event took place at all. Essentially, each observation of a case becomes a start-stop as to when the event-based variable was observed. I Illustrate below what I have and then want. Have: Time ID County FocalEvent X1mva X2police 1 222 ABC 1 10 15 2 222 ABC 0 5 10 3 222 ABC 0 15 5 4 222 ABC 0 2 4 5 222 ABC 1 5 10 6 222 ABC 0 10 5 7 222 ABC 0 5 10 8 222 ABC 0 4 4 9 222 ABC 0 5 5 10 222 ABC 1 10 10 1 333 BBB 0 3 4 2 333 BBB 0 2 1 3 333 BBB 0 5 5 4 333 BBB 1 10 5 5 333 BBB 0 15 15 6 333 BBB 0 10 10 7 333 BBB 0 10 10 8 333 BBB 1 5 5 9 333 BBB 0 10 10 10 333 BBB 0 15 15 1 444 CCC 0 1 1 2 444 CCC 0 5 5 3 444 CCC 0 4 4 4 444 CCC 0 5 5 5 444 CCC 0 1 1 6 444 CCC 1 5 5 7 444 CCC 0 10 10 8 444 CCC 0 5 5 9 444 CCC 0 5 5 10 444 CCC 0 5 5 1 555 DDD 0 1 1 2 555 DDD 0 1 1 3 555 DDD 0 1 1 Want: ID County X1mva X2police Time1 Time2 Event 222 ABC 10 15 1 1 1 222 ABC 27 34 1 5 1 222 ABC 34 34 5 10 1 333 BBB 20 15 1 4 1 333 BBB 40 40 4 8 1 333 BBB 25 25 8 10 0 444 CCC 21 21 1 6 1 444 CCC 25 25 6 10 0 555 DDD 3 3 1 3 0 Thanks for your help. R

r4321 · ‎05-27-2020

Hm... thanks for taking a look! I am not having much luck with this code, however. It seems to do okay with creating the indicators for the counties that experienced the focal event, but it doesn't do it for the ones that didnt. Also, brainstorming how to best deal with that duration variable as well. Appreciate your help.