Hello - I have a data set like the table below. My goal is, for a given ID, to find a matching ID with the conditions that: the ID characters match; and Var1(ID) = Var2(laggedID). The matching ID always precedes the given ID. My data set looks like this: DATA HAVE: ID Period Var1 Var2 Start_Lag End_Lag a1 1 500 300 . . a2 1 450 350 . . a3 2 350 300 1 2 a4 2 300 200 2 3 a5 3 300 50 1 2 a6 3 200 100 2 3 b1 1 525 175 . . b2 1 451 321 . . b3 1 226 216 . . b4 2 321 101 1 3 b5 2 216 106 2 4 b6 2 174 105 3 5 I'm trying to generate a table like this that links the ID variables. The reason I'm doing this is in order, eventually, to transpose the table into time series data, according to the linked records. So my Data WANT looks like this: DATA WANT: ID LINK a1 . a2 . a3 a2 a4 a1 a5 a3 a6 a4 b1 . b2 . b3 . b4 b2 b5 b3 b6 b1 LINK indicates which ID has the matching Var2. I've tried the following approach... % Macro TEST; Data WANT (Keep= ID LINK); Set HAVE; Threshold = 1; /*I have a threshold for Linking because I need to account for rounding error between periods - e.g. b6 --> b1*/ %Do i = Start_Lag %to End_Lag; Difference = VAR1 - Lag&i(VAR2); If Difference < Threshold then do; Link = Lag&i(D); Threshold = Difference; end; %end; run; %mend; %TEST I originally started with a Data Step approach but switched to a Macro because otherwise I can't seem to get Lag function to adjust dynamically to the different values specified in my Start_Lag and End_Lag columns. Inside of the macro the %Do loop needs macro variables, though, and I haven't been able to work this step out. I have tried using symput like this: call symput('Begin', Start_Lag); call symput('Stop', End_Lag); %Do i = &Begin %to &Stop; ... but it doesn't seem to be running properly since LINK ends up all over the place... Any ideas?
... View more
