Creating New Variables Conditional on Others [sas] -

Creating New Variables Conditional on Others [sas] -

the original dataset like:

sub month y 1 1 1 1 2 2 1 3 3 1 5 5

what want previous 3 ys based on month each subject, if y missing @ month, new variable . too. , above example, lag1 previous y lastly month, lag2 previous y 2 months ago, lag3 far forth:

sub month y lag1 lag2 lag3 1 1 1 . . . 1 2 2 1 . . 1 3 3 2 1 . 1 5 5 . 3 2

the thing checked out lag , dif function, in case, want on lag depends on month , there gaps between month, can not utilize previous 1 lag1 function.

also need many subjects too. thanks.

sql solution:

data have; input sub month y; datalines; 1 1 1 1 2 2 1 3 3 1 5 5 ;;;; run; proc sql; create table want select h.sub,h.month, h.y, one.y lag1, two.y lag2, three.y lag3 have h left bring together (select * have) 1 on h.sub=one.sub , h.month=one.month+1 left bring together (select * have) 2 on h.sub=two.sub , h.month=two.month+2 left bring together (select * have) 3 on h.sub=three.sub , h.month=three.month+3 ; quit;

obviously gets bit long if want 36 of them, it's not complex @ least. there sorts of other ways this. don't utilize lag, that's going headache , not appropriate anyway. hash table might more efficient , require less coding, if you're familiar concept of hash.

hash solution:

data want; if _n_ = 1 do; declare hash h(dataset:'have(rename=y=ly)'); h.definekey('sub','month'); h.definedata('ly'); h.definedone(); phone call missing(sub,month,ly); end; set have; array lags lag1-lag3; prevmonth = month-1 month-3 -1; if prevmonth le 0 leave; rc=h.find(key:sub,key:prevmonth); if rc=0 lags[month-prevmonth] = ly; phone call missing(ly); end; run;

this pretty simple expand 36 [or whatever] - alter length of array array lags lag1-lag36 , statement do prevmonth=month-1 month-36 -1; work may have arrange things month works here - either creating integer month, or changing loop criteria work month/year or whatnot. don't show how info specified can't help there.

sas