suppose to have the following datasets:
<code>data DB;
input ID :$20. Admission :date09. Discharge :date09. Variable;
cards;
166 16FEB2019 26FEB2019 1
170 13JAN2017 25JAN2017 0
170 22FEB2017 07MAR2017 1
170 27APR2017 16MAY2017 1
170 30JAN2019 04MAR2019 0
313 03MAR2016 10MAR2016 1
;
run;
data DB1;
input ID :$20. Admission :date09. Discharge :date09. Variable_;
cards;
166 16FEB2019 26FEB2019 1
166 16FEB2019 26FEB2019 1
166 16FEB2019 26FEB2019 1
166 16FEB2019 26FEB2019 1
166 16FEB2019 26FEB2019 1
166 16FEB2019 26FEB2019 1
170 22FEB2017 07MAR2017 0
170 13JAN2017 25JAN2017 0
170 30JAN2019 04MAR2019 1
170 13JAN2017 25JAN2017 0
170 30JAN2019 04MAR2019 1
170 27APR2017 16MAY2017 1
170 27APR2017 16MAY2017 1
170 27APR2017 16MAY2017 1
170 27APR2017 16MAY2017 1
170 27APR2017 16MAY2017 1
313 03MAR2016 10MAR2016 0
313 03MAR2016 10MAR2016 0
313 03MAR2016 10MAR2016 0
;
run;
</code>
<code>data DB;
input ID :$20. Admission :date09. Discharge :date09. Variable;
cards;
166 16FEB2019 26FEB2019 1
170 13JAN2017 25JAN2017 0
170 22FEB2017 07MAR2017 1
170 27APR2017 16MAY2017 1
170 30JAN2019 04MAR2019 0
313 03MAR2016 10MAR2016 1
;
run;
data DB1;
input ID :$20. Admission :date09. Discharge :date09. Variable_;
cards;
166 16FEB2019 26FEB2019 1
166 16FEB2019 26FEB2019 1
166 16FEB2019 26FEB2019 1
166 16FEB2019 26FEB2019 1
166 16FEB2019 26FEB2019 1
166 16FEB2019 26FEB2019 1
170 22FEB2017 07MAR2017 0
170 13JAN2017 25JAN2017 0
170 30JAN2019 04MAR2019 1
170 13JAN2017 25JAN2017 0
170 30JAN2019 04MAR2019 1
170 27APR2017 16MAY2017 1
170 27APR2017 16MAY2017 1
170 27APR2017 16MAY2017 1
170 27APR2017 16MAY2017 1
170 27APR2017 16MAY2017 1
313 03MAR2016 10MAR2016 0
313 03MAR2016 10MAR2016 0
313 03MAR2016 10MAR2016 0
;
run;
</code>
data DB;
input ID :$20. Admission :date09. Discharge :date09. Variable;
cards;
166 16FEB2019 26FEB2019 1
170 13JAN2017 25JAN2017 0
170 22FEB2017 07MAR2017 1
170 27APR2017 16MAY2017 1
170 30JAN2019 04MAR2019 0
313 03MAR2016 10MAR2016 1
;
run;
data DB1;
input ID :$20. Admission :date09. Discharge :date09. Variable_;
cards;
166 16FEB2019 26FEB2019 1
166 16FEB2019 26FEB2019 1
166 16FEB2019 26FEB2019 1
166 16FEB2019 26FEB2019 1
166 16FEB2019 26FEB2019 1
166 16FEB2019 26FEB2019 1
170 22FEB2017 07MAR2017 0
170 13JAN2017 25JAN2017 0
170 30JAN2019 04MAR2019 1
170 13JAN2017 25JAN2017 0
170 30JAN2019 04MAR2019 1
170 27APR2017 16MAY2017 1
170 27APR2017 16MAY2017 1
170 27APR2017 16MAY2017 1
170 27APR2017 16MAY2017 1
170 27APR2017 16MAY2017 1
313 03MAR2016 10MAR2016 0
313 03MAR2016 10MAR2016 0
313 03MAR2016 10MAR2016 0
;
run;
Is there a way to compare the Variable_ value in DB1 with Variable in DB at corresponding date and BY ID? As you can see in DB1 some dates are replicated and when it happens the value of Variable_ is also replicated so no chance to have 1 or 0 when dates are replicated but always 1 or always 0. the desired output should be:
<code>data DB2;
input ID :$20. Admission :date09. Discharge :date09. Variable New_Var;
cards;
166 16FEB2019 26FEB2019 1 0
170 13JAN2017 25JAN2017 0 0
170 22FEB2017 07MAR2017 1 0
170 27APR2017 16MAY2017 1 0
170 30JAN2019 04MAR2019 0 1
313 03MAR2016 10MAR2016 1 0
;
run;
</code>
<code>data DB2;
input ID :$20. Admission :date09. Discharge :date09. Variable New_Var;
cards;
166 16FEB2019 26FEB2019 1 0
170 13JAN2017 25JAN2017 0 0
170 22FEB2017 07MAR2017 1 0
170 27APR2017 16MAY2017 1 0
170 30JAN2019 04MAR2019 0 1
313 03MAR2016 10MAR2016 1 0
;
run;
</code>
data DB2;
input ID :$20. Admission :date09. Discharge :date09. Variable New_Var;
cards;
166 16FEB2019 26FEB2019 1 0
170 13JAN2017 25JAN2017 0 0
170 22FEB2017 07MAR2017 1 0
170 27APR2017 16MAY2017 1 0
170 30JAN2019 04MAR2019 0 1
313 03MAR2016 10MAR2016 1 0
;
run;
In other words a new variable should be created in DB, called “New_Var” equal to 1 only if Variable_ = 1 in DB1 and Variable = 0 in DB.
Thank you in advance
Try this
<code>data want(drop = rc Variable_);
if _N_ = 1 then do;
dcl hash h(dataset : 'DB1');
h.definekey('ID', 'Admission', 'Discharge');
h.definedata('Variable_');
h.definedone();
end;
set DB;
Variable_ = .;
rc = h.find();
New_Var = (Variable = 0 & Variable_ = 1);
run;
</code>
<code>data want(drop = rc Variable_);
if _N_ = 1 then do;
dcl hash h(dataset : 'DB1');
h.definekey('ID', 'Admission', 'Discharge');
h.definedata('Variable_');
h.definedone();
end;
set DB;
Variable_ = .;
rc = h.find();
New_Var = (Variable = 0 & Variable_ = 1);
run;
</code>
data want(drop = rc Variable_);
if _N_ = 1 then do;
dcl hash h(dataset : 'DB1');
h.definekey('ID', 'Admission', 'Discharge');
h.definedata('Variable_');
h.definedone();
end;
set DB;
Variable_ = .;
rc = h.find();
New_Var = (Variable = 0 & Variable_ = 1);
run;
Result:
<code>ID Admission Discharge Variable New_Var
166 16FEB2019 26FEB2019 1 0
170 13JAN2017 25JAN2017 0 0
170 22FEB2017 07MAR2017 1 0
170 27APR2017 16MAY2017 1 0
170 30JAN2019 04MAR2019 0 1
313 03MAR2016 10MAR2016 1 0
</code>
<code>ID Admission Discharge Variable New_Var
166 16FEB2019 26FEB2019 1 0
170 13JAN2017 25JAN2017 0 0
170 22FEB2017 07MAR2017 1 0
170 27APR2017 16MAY2017 1 0
170 30JAN2019 04MAR2019 0 1
313 03MAR2016 10MAR2016 1 0
</code>
ID Admission Discharge Variable New_Var
166 16FEB2019 26FEB2019 1 0
170 13JAN2017 25JAN2017 0 0
170 22FEB2017 07MAR2017 1 0
170 27APR2017 16MAY2017 1 0
170 30JAN2019 04MAR2019 0 1
313 03MAR2016 10MAR2016 1 0