I have a dataset like this
Id param vaL date1 date2
101 A 2.4 02jan2019 05jan2019
101 A 4 05jan2019 05jan2019
101 A 5 10jan2019 05jan2019
101 B 1 03MAY2018 0APR2019
101 B 8 04AUG2017 05SEP2017
101 C 2 06FEB2016 01JAN2016
101 C 5 08MAR2014 08AUG2018
101 C 9 07APR2017 09MAR2018
102 A 10 09SEP2016 09DEC2015
102 A 4 05jan2019 15jan2019
.......................................SO ON.....
I want to derive a flag variable , as per the conditions;
The flag should be populated for each param within each id;(only once as the conditions are satisfied for the firsttime)
Also date1 < date2 like
d param vaL date1 date2 FLAG
101 A 2.4 02jan2019 05jan2019 Y
101 A 4 05jan2019 05jan2019
101 A 5 10jan2019 05jan2019
101 B 1 03MAY2018 0APR2019
101 B 8 04AUG2017 05SEP2017 Y
101 C 2 06FEB2016 01JAN2017
101 C 5 08MAR2014 08AUG2018 Y
101 C 9 07APR2017 09MAR2018
102 A 10 09SEP2016 09DEC2015
102 A 4 05jan2019 15jan2019 Y
.......................................SO ON.....
After making code out of your data, I found and fixed two typos in my code (supplying example data in usable form - data step with datalines - makes it easy for us to test code):
data have;
input
Id $ param $ val date1 :date9. date2 :date9.;
format date1 date2 yymmddd10.;
datalines;
101 A 2.4 02jan2019 05jan2019
101 A 4 05jan2019 05jan2019
101 A 5 10jan2019 05jan2019
101 B 1 03MAY2018 05APR2019
101 B 8 04AUG2017 05SEP2017
101 C 2 06FEB2016 01JAN2016
101 C 5 08MAR2014 08AUG2018
101 C 9 07APR2017 09MAR2018
102 A 10 09SEP2016 09DEC2015
102 A 4 05jan2019 15jan2019
;
data want;
set have;
by id param;
retain _flag;
if first.param then _flag = 0;
if date1 < date2 and not _flag
then do;
flag = 'Y';
_flag = 1;
end;
drop _flag;
run;
proc print data=want noobs;
run;
Result:
Id param val date1 date2 flag 101 A 2.4 2019-01-02 2019-01-05 Y 101 A 4.0 2019-01-05 2019-01-05 101 A 5.0 2019-01-10 2019-01-05 101 B 1.0 2018-05-03 2019-04-05 Y 101 B 8.0 2017-08-04 2017-09-05 101 C 2.0 2016-02-06 2016-01-01 101 C 5.0 2014-03-08 2018-08-08 Y 101 C 9.0 2017-04-07 2018-03-09 102 A 10.0 2016-09-09 2015-12-09 102 A 4.0 2019-01-05 2019-01-15 Y
Note that for id=101, param=B, the first observation satisfies the condition, as date1 is in 2018 and date2 in 2019.
This?
data want;
set have;
by id param;
retain _flag;
if first.param then flag = 0;
if date1 < date2 and not _flag;
then do;
flag = 'Y';
_flag = 1;
end;
drop _flag;
run;
hi,
I am not getting proper output. I want 3 flags for each id because there are 3 param values(A,B,C) as per the dataset.
After making code out of your data, I found and fixed two typos in my code (supplying example data in usable form - data step with datalines - makes it easy for us to test code):
data have;
input
Id $ param $ val date1 :date9. date2 :date9.;
format date1 date2 yymmddd10.;
datalines;
101 A 2.4 02jan2019 05jan2019
101 A 4 05jan2019 05jan2019
101 A 5 10jan2019 05jan2019
101 B 1 03MAY2018 05APR2019
101 B 8 04AUG2017 05SEP2017
101 C 2 06FEB2016 01JAN2016
101 C 5 08MAR2014 08AUG2018
101 C 9 07APR2017 09MAR2018
102 A 10 09SEP2016 09DEC2015
102 A 4 05jan2019 15jan2019
;
data want;
set have;
by id param;
retain _flag;
if first.param then _flag = 0;
if date1 < date2 and not _flag
then do;
flag = 'Y';
_flag = 1;
end;
drop _flag;
run;
proc print data=want noobs;
run;
Result:
Id param val date1 date2 flag 101 A 2.4 2019-01-02 2019-01-05 Y 101 A 4.0 2019-01-05 2019-01-05 101 A 5.0 2019-01-10 2019-01-05 101 B 1.0 2018-05-03 2019-04-05 Y 101 B 8.0 2017-08-04 2017-09-05 101 C 2.0 2016-02-06 2016-01-01 101 C 5.0 2014-03-08 2018-08-08 Y 101 C 9.0 2017-04-07 2018-03-09 102 A 10.0 2016-09-09 2015-12-09 102 A 4.0 2019-01-05 2019-01-15 Y
Note that for id=101, param=B, the first observation satisfies the condition, as date1 is in 2018 and date2 in 2019.
thank u 🙂
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.