Hello, Here is the sample data set similar to what I am handling now. data temp;
infile datalines dlm='#';
input id $ dttm :datetime20. var1 var2 var3 var4;
format dttm datetime20.;
datalines;
A # 20JAN2021:13:37:39.000 # 1 # # #
A # 10FEB2021:09:45:46.000 # 1 # 0 # 0 # 0
A # 04SEP2021:11:17:24.000 # 1 # # #
A # 09OCT2021:18:01:40.000 # 1 # 0 # 0 # 0
A # 10OCT2021:06:15:52.000 # 0 # # #
A # 13OCT2021:13:42:22.000 # 1 # # #
A # 17OCT2021:10:44:29.000 # 0 # 0 # #
A # 11DEC2021:09:54:47.000 # 0 # 1 # 1 #
A # 15DEC2021:16:55:44.000 # 1 # 0 # 0 #
A # 15MAY2022:11:33:08.000 # 0 # # #
;
run; Here are the codes I wrote. data temp1;set temp;
by id;
length var_combined $20.;
if first.id then do;
ct_one=.;
ct_zero=.;
end;
var_combined=catx(', ', of var:);
ct_one=count(var_combined, '1');
ct_zero=count(var_combined, '0');
if (ct_one>0 and ct_zero=0) then outcome=1;
if (ct_one=0 and ct_zero>0) then outcome=0;
if (ct_one>0 and ct_zero>0) then outcome=1-lag(outcome);
run; Explanations. I combined var1-var4 (var_combined) since I need to know whether all vars have the same value (e.g., all zeros or all ones). If one and zero are mixed, then the outcome needs to be the opposite of the value on the previous date. For instance, if the outcome for obs#1 is 1, then the outcome for obs#2 needs to be 0, not 1. I used the lag function together with the if statement, but it's not working. My codes assign a missing value for the obs with ones and zeros mixed. I understand this conditional lag() will work at the execution level, not the case level and that's why I get a missing value for the lagged outcome. Still, I cannot figure out how to correct the codes to achieve what I want. FYI, I don't have to use this approach for the goal. Any advice or help would be greatly appreciated! Please let me know if you need additional information. Thank you.
... View more