Hi, there
I am curious how to achieve this in SAS. My data is sorted by id and date. for id "a", if the days between date in row 3 and row 4 is equal to 3, then I want to copy var1 to var2. Otherwise, in case id"b", if the days between date in row 3 and row 4 is not equal to 3, then leave it blank.
Thanks in advance!!!
data have;
infile cards dlm=',';
Input ID$ date :ddmmyy10. var1 var2;
format date YYMMDDn8.;
cards;
a, 01/01/2018, . ,111,
a, 02/01/2018, . ,111,
a, 03/01/2018, . ,111,
a, 06/01/2018, 222 ,.,
a, 07/01/2018, 222 ,.,
b, 08/01/2018, . ,111,
b, 09/01/2018, . ,111,
b, 10/01/2018, . ,111,
b, 11/01/2018, 222 ,.,
b, 12/01/2018, 222 ,.,
;
run;
data want;
infile cards dlm=',';
Input ID$ date :ddmmyy10. var1 var2;
format date YYMMDDn8.;
cards;
a, 01/01/2018, . ,111,
a, 02/01/2018, . ,111,
a, 03/01/2018, . ,111,
a, 06/01/2018, 222 ,222,
a, 07/01/2018, 222 ,.,
b, 08/01/2018, . ,111,
b, 09/01/2018, . ,111,
b, 10/01/2018, . ,111,
b, 11/01/2018, 222 ,.,
b, 12/01/2018, 222 ,.,
;
run;
data want;
set have;
by id;
prev_date=lag(date);
if date-prev_date=3 and not first.id then var2=var1;
drop prev_date;
run;
data have;
infile cards dlm=',';
Input ID$ date :ddmmyy10. var1 var2;
format date YYMMDDn8.;
cards;
a, 01/01/2018, . ,111,
a, 02/01/2018, . ,111,
a, 03/01/2018, . ,111,
a, 06/01/2018, 222 ,.,
a, 07/01/2018, 222 ,.,
b, 08/01/2018, . ,111,
b, 09/01/2018, . ,111,
b, 10/01/2018, . ,111,
b, 11/01/2018, 222 ,.,
b, 12/01/2018, 222 ,.,
;
run;
data want;
do _n_=1 by 1 until(last.id);
set have;
by id;
if _n_=4 and date-lag(date)=3 then var2=var1;
output;
end;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.